rollback segment problem

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

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...

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#.

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

Quote:

---

[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?

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.