Re: Unable to take rollback segment offline

Bill,

There's an active tx using that rbs. The rbs won't go offline until there are no more active tx's using that rbs. Check v$transaction and v$lock to find out more info.

HTH,

• Anita
  • Bill Wagman <wjwagman_at_ucdavis.edu> wrote:
    > 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
    >
    >

    ---

    
    > 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