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
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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

734 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