Q

Clearing the rollback segment from a tablespace

I have a rollback tablespace that shows max bytes of .30 out of 500M, with 499.69M used. I have taken my database down twice to clear the rollback segment from this tablespace and it won't go away. Is there a way to free up the space other than dropping the tablespace and recreating it?

I have a rollback tablespace that shows max bytes of .30 out of 500M, with 499.69M used. I have taken my database down twice to clear the rollback segment from this tablespace and it won't go away. It appears to be held by one of the users that show up as '1' when you run the select from v$session.

I've run the following code and got back "no rows selected":

 
select lpad(' ',DECODE(L.XIDUSN,0,3,0))

||L.ORACLE_USERNAME "USER NAME",

O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE

FROM V_$LOCKED_OBJECT L, DBA_OBJECTS O

WHERE L.OBJECT_ID = O.OBJECT_ID
 
ORDER BY O.OBJECT_ID, 1 DESC;

I can take the segment offline, but I can't take the tablespace offline because the segment shows up as being active. I can't drop the segment either. I get this message: "rollback segment 'ROLL28_RBS' specified not available." Is there a way to free up the space other than dropping the tablespace and recreating it?

This is easily solved with the following steps:

  1. Shut down your database.
  2. Take the rollback segment out of your ROLLBACK_SEGMENTS initialization parameter.
  3. Start the database.
  4. You can now drop the rollback segment and recreate it.

Another option is to modify the rollback segment's OPTIMAL parameter so that it will shrink back to the optimal size once the space is no longer needed. And you can manually shrink the rollback segment with the ALTER ROLLBACK SEGMENT SHRINK command.

If you are using Oracle 9i or higher, then I highly recommend using Automated Undo and letting Oracle manage these rollback segments for you.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close