Re: Unable to take rollback segment offline

Winnie,

Thanks. Now I see that the status of the rollback segment is in fact PENDING OFFLINE. Is it possible to determine why it is pending?

Thanks.

At 10:09 AM 6/8/00 -0800, you wrote:
>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
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

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