Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unable to take rollback segment offline

Re: Unable to take rollback segment offline

From: <>
Date: Thu, 8 Jun 2000 09:42:44 -0700
Message-Id: <>

Very likely that your rollback segment is not offline, but offline pending. That means some body still have active transaction in that rollback segment. Once they commit/rollback, Oracle will clean it up and bring it offline.


Bill Wagman <> on 06/08/2000 09:47:29 AM

Please respond to

To: Multiple recipients of list ORACLE-L <> cc: (bcc: Winnie Liu/HQ/ISC)

Subject: Unable to take rollback segment offline


In a database used for a database class it became necessary to restructure the rollback tablespace and segments. I started off with a 100MB rollback tablespace with 10 rollback segments, all equally sized. I first increased the size of the rollback tablespace to 600MB and then resized rollback segment r01 so it could use the entire tablespace. I then attempted to take rollback segments r02 through r10 offline and drop them. All worked fine except for one rollback segment, r08. In server manager I issue the command alter rollback segment r08 offline; and server manager replies the statement is processed but when I query dba_rollback_segs r08 is still shown as online. As far as I can determine (using suggestions in Kevin Loney's book of SQL and PL/SQL scripts, other scripts appreciated) there are no active transactions using rollback segment r08. When I attempt to drop rollback segment r08 I receive ORA-01545: rollback segment r08 specified not available. The Enterprise Manager reports it being offline if I view all the rollback segments in the storage manager window but if I look specifically at rollback segment r08, again in OEM, it reports it being online. Oracle support says there are two undocumented parameters which must be set, and they will only do it with you over the phone
(wonderful if they won't answer the phone any more) and won't pass out the
documentation as the risk of corrupting the database exists. Because the database is actively in use at this time, a project is due shortly, I can't find a time immediately to bounce the database and Oracle said I should be concerned about it not coming up anyway.

So... has anyone seen this problem and if so any advice, suggestions, help would be most appreciated.


Bill Wagman
Univ. of California at Davis
Information Resources
(530) 754-6208

Why is it that when you're driving and looking for an address, you turn
down the volume on the radio?

Author: Bill Wagman

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
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 Thu Jun 08 2000 - 11:42:44 CDT

Original text of this message