The offline of the rollback is "PENDING" that means Oracle is waiting for
either a commit or a rollback on a transaction in the rollback segment.
When the offending transaction commits or rolls back, the rollback segment
will go offline. To automate this procedure, you almost need a pl/sql
cursor loop to wait until the rollback segment is offline before you do
anything with it. I generally take the rollback segments offline manually
and then wait until their status truly says OFFLINE.
> -----Original Message-----
> From: Winnie_Liu_at_infonet.com [SMTP:Winnie_Liu_at_infonet.com]
> Sent: Thursday, June 08, 2000 1:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Unable to take rollback segment offline
>
> To demonstate it more clearly about what I've just said.
>
> In one section, I am currently using a rollback segment to do a insert
> without any commit/rollback/DML statement afterwards.
>
> In another section, I alter that rollback segment offline.
>
> And this is what I get,
>
> 09:51:07 WLIU( @kingtut) > select * From dba_rollback_segs where
> segment_name='RB11';
>
> SEGMENT_NAME OWNER TABLESPACE_NAME
> SEGMENT_ID FILE_ID BLOCK_ID
> ------------------------------ ------ ------------------------------
> ------
> ---- --------- ---------
> INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
> -------------- ----------- ----------- ----------- ------------
> -----------
> -----
> INSTANCE_NUM RELATIVE_FNO
> ---------------------------------------- ------------
> RB11 PUBLIC RBS
> 12 3 1752
> 102400 256000 2 121 0 ONLINE
> 3
>
>
> 1 row selected.
>
> 09:51:22 WLIU( @kingtut) > select * from v$rollstat where usn=12;
>
> USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
> OPTSIZE HWMSIZE SHRINKS
> --------- --------- --------- --------- --------- --------- ---------
> -----
> ---- --------- ---------
> WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT
> CURBLK
> --------- --------- --------- --------- --------------- ---------
> ---------
> 12 3 612352 2316 1 4035 0
> 512000 612352 0
> 0 0 0 0 PENDING OFFLINE 1
> 96
>
>
> 1 row selected.
>
> The status of the rollback is now PENDING OFFLINE
>
> Winnie
>
>
>
>
>
>
>
> Bill Wagman <wjwagman_at_ucdavis.edu> on 06/08/2000 09:47:29 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc: (bcc: Winnie Liu/HQ/ISC)
>
>
>
>
>
> Hello,
>
> 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.
>
> Thanks.
>
> Bill Wagman
> Univ. of California at Davis
> Information Resources
> wjwagman_at_ucdavis.edu
> (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
> INET: wjwagman_at_ucdavis.edu
>
> 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: ListGuru_at_fatcity.com (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).
>
>
>
>
>
>
> --
> Author:
> INET: Winnie_Liu_at_infonet.com
>
> 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Thu Jun 08 2000 - 12:40:16 CDT
