Solved

Clearing RBS Tablespace

Posted on 2001-08-07
4
2,234 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
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Wadhwa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thaks, dbalaski. Just what I've been looking for
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

771 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

10 Experts available now in Live!

Get 1:1 Help Now