Re: rollback segment

[Daniel W. Fink]

You can take the rollback segment offline. It will not impact any currently running transactions and no new transactions will be assigned to it. Oracle will not let you drop a rollback segment while there are any active transactions writing undo to the rollback segment. IIRC, the v$rollstat.status goes to 'PENDING OFFLINE'  when you set if offline, but transactions are still using it. It then changes to 'OFFLINE' when the pending transactions are completed.

One caveat is that the status is unaffected by consistent read operations. It is possible to drop the rollback segment and have queries fail with Snapshot Too Old. My recommendation is to offline the segment during a time of low activity and then drop it.

v$transaction provides limited undo info, but enough to determine which sessions/users are using the specific rollback segment.
   

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals

AK wrote:

I have recently found that one temporary rollback segment in system tablespace is having more then 600 extnets . It looks like previous dba created this rollback segment at the time of db creation and forgot to take it offline .

Now how do I find it this rollback segment is being used actively . I see values in gets , writes from v$rollstat view .

 

What step should I take to drop this rollback segment . What implication might have if I take this offline ? I want to relase the space taken by this rollback segment ( >600 extents ..thats big ) , so just taking offline wont do this.

 

Are there any views accessible from system( no sys) account which can tell me which trasaction is using which rollback segment .

 

 

thanks

-ak