I've run the following code and got back "no rows selected":
select lpad(' ',DECODE(L.XIDUSN,0,3,0)) ||L.ORACLE_USERNAME "USER NAME", O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE FROM V_$LOCKED_OBJECT L, DBA_OBJECTS O WHERE L.OBJECT_ID = O.OBJECT_ID ORDER BY O.OBJECT_ID, 1 DESC;
I can take the segment offline, but I can't take the tablespace offline because the segment shows up as being active. I can't drop the segment either. I get this message: "rollback segment 'ROLL28_RBS' specified not available." Is there a way to free up the space other than dropping the tablespace and recreating it?
- Shut down your database.
- Take the rollback segment out of your ROLLBACK_SEGMENTS initialization parameter.
- Start the database.
- You can now drop the rollback segment and recreate it.
Another option is to modify the rollback segment's OPTIMAL parameter so that it will shrink back to the optimal size once the space is no longer needed. And you can manually shrink the rollback segment with the ALTER ROLLBACK SEGMENT SHRINK command.
If you are using Oracle 9i or higher, then I highly recommend using Automated Undo and letting Oracle manage these rollback segments for you.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading