NOTE, although automatic undo and undo tablespace is used by the database, the following procedure is the same. The only difference is that the segment names are assigned by Oracle.
1. Check for any rollback segments online.
SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = '';
If any of the rollbacks you tried to offline still has an "ONLINE" status, this is usually an indication that this segment contains active transactions.
2. Check for active transactions with the following query:
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'UNDOTBS1' AND SEGMENT_ID = USN;
If the above query returns no rows, it means all the rollbacks in the affected tablespace are already offline. Repeat the query in step 1 to retrieve the names of the rollbacks that just became offline and attempt to drop the undo tablespace or individual rollback segments as described above.
If the above query returns one or more rows, they should show status "PENDING OFFLINE". Next, check the ACTIVE_TX column for each rollback. If it has a value of 0, it implies there are no pending transactions left in the rollback, and it should go offline shortly. Repeat the query in step 1 a few more times until it shows the rollback segments being offline and then attempt the drop again. Once the drop is successful, you are done.
If any of the "pending offline" rollbacks has a value of 1 or greater in the ACTIVE_TX column, move on to step 3.
3. Force rollbacks with active transactions to go offline.
At this point, the only way to move forward is to have the "pending offline" rollbacks released. The active transactions in these rollbacks must either be committed or rolled back.
The following query shows which users have transactions assigned to which rollbacks:
SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('', ... , '')
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;
You may directly contact the users with transactions in the "pending offline" rollbacks and ask them to commit (preferably) or rollback immediately. If that is not feasible, you can force that to happen by killing their sessions. For each of the entries returned by the above query, issue the statement:
SQL> ALTER SYSTEM KILL SESSION ', ';
Where and are those returned by the previous query. After the sessions are killed, it may take a few minutes before Oracle finishes rolling back and doing cleanup work. Go back to step 1 and repeat the query in there periodically until all rollbacks in the affected tablespace are offline and ready to be dropped.
If you are unable to drop the older undo tablespace or rollback segments, try to restore the file from backup and recover it fully (approach II.B). Once the file is recovered try to drop the older undo tablespace. If this is not possible, contact Oracle Support Services.
