Solved

Clearing RBS Tablespace

Posted on 2001-08-07
4
2,362 Views
Last Modified: 2008-02-01
Well, I want to clean up my RBS tablespace. It's big, and it's full, and it doesen't get freed after shutdown/startup. It is very fragmented too.

Calling "alter tablespace RBS temporary" got me only "ORA-01662: tablespace 'RBS' is non-empty and cannot be made temporary". I definitely agree it is not empty. I can see it. It is 95.5% used. And it is as big as the half of the database! OK, it is not problem being so big. But why so full even right after startup?

How can I clean up my RBS tablespace?

Rgds,
Frodo
0
Comment
Question by:FrodoBeggins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6361295
Maka all the rollback segments offline

select segment_name,tablespace_name from dba_rollback_Segs
where tablespace_name='RBS';

alter rollback segment segment_name offline;

once you offline all the rollback segments;

drop rbs tablespace by drop tablespace tablespacename;

create it again and all the rollback segment. once you create a rollback segment you have to make online.

Hope this will help
Sameer

0
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
ID: 6361447
Hi,

I think I see what is going on.

First:
Rollback Tablespace needs to be of the type Permanent.
Rollback Segments are permanent Objects, therefore cannot exist in a temporary tablespace type.

Second:
When rollbacks extend for a transaction,
by default they do not automatically shrink back
when the transactions have completed.  
Thus, they remain extended -- even though they are mostly empty!

So -- this leads to the question:
How do I get them to deallocate this space once they have completed their transaction??

There are two ways to force the rollback segment to shrink after a transaction is completed:

1) manually decrease the size of a rollback segment using the following command:
alter rollback segment <SEGMENT NAME> shrink to <size in M/K>;

2) alter the rollback segment to have the OPTIMAL parameter set.  

From the Manual:
Specify SHRINK if you want Oracle to attempt to shrink the rollback segment to an optimal or specified size. The success and amount of shrinkage depend on the available free space in the rollback segment and how active transactions are holding space in the rollback segment.

The value of integer is in bytes, unless you specify K or M for kilobytes or megabytes.

If you do not specify TO integer, then the size defaults to the OPTIMAL value of the storage_clause of the CREATE ROLLBACK SEGMENT statement that created the rollback segment. If OPTIMAL was not specified, then the size defaults to the MINEXTENTS value of the storage_clause of the CREATE ROLLBACK SEGMENT statement.

Regardless of whether you specify TO integer:
* The value to which Oracle shrinks the rollback segment is valid for the execution of the statement. Thereafter, the size reverts to the OPTIMAL value of the CREATE ROLLBACK SEGMENT statement.
* The rollback segment cannot shrink to less than two extents.

For example:  
create rollback segment R01
    tablespace RBSA
    storage (initial 1M next 1M
             minextents 19 maxextents 40 optimal 21M);

The above statement creates a rollback that will automatically shrink back to 21M  if it extends past that.

If the rollback already exists,  then you can simply alter it:
alter rollback segment R01
     storage (optimal 21M);


May I suggest you review the following:
Oracle8i Administrator's Guide  Release 2 (8.1.6)
Chapter 11 Managing Rollback Segments
(has discussion of this in various placed throughout the chapter)


Hope this answers your question,
sincerely,
dbalaski
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6361458
Oh also the following:

Oracle8i SQL Reference Release 3 (8.1.7)

Command section:
*  ALTER ROLLBACK SEGMENT
  (decent discussion on this)


dB
0
 
LVL 2

Author Comment

by:FrodoBeggins
ID: 6361541
Thaks, dbalaski. Just what I've been looking for
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question