rollback segment problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: rollback segment problem

  1. #1
    Join Date
    Mar 2001
    Posts
    65
    mine is 8.1.6 on hpux 11.0

    i've got stuck with rollback segment problem

    i offlined a rbs but couldn't drop it because of pending trans.
    but the dropping failed even when there were no
    users or trans on the system.

    v$rollstat says it's pending offline with xact as 1.
    dba_rollback_segs say it's online.

    v$transaction has nothing against that rbs.

    now iam even not able to drop the rbs tablespace
    because of error "rbs with active rollback segment exist"

    and unfortunately this rbs has 1300 extents of 3m each allocated to it and tied up in datafile.



    ajay


  2. #2
    Join Date
    Apr 2001
    Posts
    17
    Try taking the tablespace offline if not successful then try using immediate or drop option:

    ALTER DATABASE DATAFILE 'blah.dbf' OFFLINE IMMEDIATE;
    ALTER DATABASE DATAFILE 'blah.dbf' OFFLINE DROP;

    After the datafiles have been taken offline, you should be able to drop the tablespace.

    Take into consideration your backups on the DB if any...

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Common bug: Kill the non-existing session ALTER SYSTEM KILL SESSION 'sid,serial#'; Use v$session and v$transaction to find the right sid and serial#.

  4. #4
    Join Date
    Mar 2001
    Posts
    65
    thanks,

    now after a gap of 2 hours i found the status of the rbs as offline and could drop it.


    julian, i was not getting anything for that rbs under v$transaction.


    ajay


  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    [i]julian, i was not getting anything for that rbs under v$transaction.
    [/B]
    Then dropping the RBS file is the solution. If in the future something happens to a RBS file, do not shutdown abort. Try immediate. With abort the recovery process is a bit more difficult.

    Were all you RBSs same size?

  6. #6
    Join Date
    Mar 2001
    Posts
    65


    since i could drop the offending rbs i didn't need to go further.

    all my rbs are sabe sized and the tablespace is having locally managed extents.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width