Clearing RBS Tablespace

Posted on 2001-08-07
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?

Question by:FrodoBeggins
  • 2

Expert Comment

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


Accepted Solution

dbalaski earned 50 total points
ID: 6361447

I think I see what is going on.

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

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,

Expert Comment

ID: 6361458
Oh also the following:

Oracle8i SQL Reference Release 3 (8.1.7)

Command section:
  (decent discussion on this)


Author Comment

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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now