Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: rollback segment

Re: rollback segment

From: Daniel W. Fink <>
Date: Fri, 14 Mar 2003 14:29:21 -0800
Message-ID: <>

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

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
-- Please see the official ORACLE-L FAQ: -- Author: Daniel W. Fink INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Fri Mar 14 2003 - 16:29:21 CST

Original text of this message