[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2518
  • Last Modified:

Clearing RBS Tablespace

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?

  • 2
1 Solution
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


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,
Oh also the following:

Oracle8i SQL Reference Release 3 (8.1.7)

Command section:
  (decent discussion on this)

FrodoBegginsAuthor Commented:
Thaks, dbalaski. Just what I've been looking for

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now