How do I drop a tablespace with an active rollback segment?

1) Why do you need to drop it? You should have at least one UNDO tablespace in your database?

2) Show your current config, I assume you are using automatic UNDO management:

SQL> show parameter undo_



3) "ORACLE not available"

You have likely not set your ORACLE_SID correctly. Verify ORACLE_HOME and ORACLE_SID are set and run sqlplus:

sqlplus / as sysdba
SQL> shutdown immediate;

Anytime you see Oracle not available then either Oracle is down or you've not set the environment variables above to the right database.

The short answer is you don't drop an active rollback / undo segment. It is critical to the operation / consistency of your database. You must disconnect all sessions to do this. Easiest way is shutdown immediate, then startup mount, then drop it.

1) I stupidly ran a stored procedure before reading it and it was trying to insert 144,000,000 rows into a table as one single transaction.  My UNDO tablespace blew up to about 6 Gigs so I created a second one and am trying to drop the first one, letting the second "take over".

2)
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS_02

3)  I had ORACLE_HOME set but not ORACLE_SID.  I set it, ran SQL*Plus, issued

shutdown immediate;

it said "ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\SALIKB\UNDOTBS01.DBF'

then I did the following and got the following error:
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU9$' found, terminate dropping
tablespace

I don't think it successfully shut down the instance - I was able to query a table.

I tried "shutdown abort" and it said "ORACLE instance shut down", but then when I tried to drop the tablespace I got the "ORACLE not available" error again.

Any other thoughts?

Regardless of whether you shutdown abort, you still have an active UNDO that is not finished. I assumed the UNDO was for some transient transaction, but now that you clarified, you MUST let it finish. You cannot drop the segment, it is in the middle of a rollback.

Two things are critical to your database, redo and undo. Undo is reversing all the changes from that procedure, you cannot drop it or you lose your database. Yes, its taking a while but the only other option would be to shutdown abort, then restore / recover or use flashback database to a previous SCN, assuming you are using flashback option.

Make sure to refresh your knowledge on how UNDO and COMMIT works. When you are issuing DML changes in Oracle the changes are actually writing out to the REDO log buffer, and are writing to the DATAFILE PRIOR to the commit. In the event of a failure or rollback, all of the UNDO segment is used to return your DATAFILE blocks to their consistent state, prior to the start of the transaction.

Your options are either:
1) Let it finish
2) Restore & recover from a previous backup and I do not suggest that is where you want to go unless this is just a play development database :)

Also the database should be re-opened or the UNDO will never finish.

This is just a play/development database.  And I haven't taken any backups of it because there was nothing really important in it.

I did, in all honesty, let it run for about an hour, and when I realized there were no transactions involved, I clicked the Stop button (this was in SQL Navigator), let it "stop" for awhile, and end-tasked it, and rebooted.  So it did not get a chance to finish.

In this case, do I really have no choice but to either drop and re-create the database or let it chew up the 6 Gigs of space on my harddrive?

"Also the database should be re-opened or the UNDO will never finish."

So how do I "re-open" the database to "let the UNDO finish"?  By the way, in my list of rollback segments there is a segment marked as "needs recovery"

>>In this case, do I really have no choice but to either drop and re-create the database or let it chew up the 6 Gigs of space on my harddrive?

Pardon the expression, but you are not clear on what is going on. You've _already_ chewed up the 6GB. All of that transaction is sitting in the UNDO segment at this time. There is no new data being created, it is in the middle of the reversal. The reversal (rollback) will resume as soon as you open your DB again.

If you let it finish, then you can at that point either:
1) Drop the tablespace and recreate
2) Resize the tablespace back to a reasonable size


>>how do I "re-open" the database to "let the UNDO finish"

sqlplus / as sysdba
SQL> startup;

It will open, and then Oracle will continue in the media recovery process by resuming the UNDO.

so when I got impatient and shut down the laptop while it was still backing out the huge transaction, upon restarting the laptop and restarting the Oracle instance Oracle would have automatically continued to back out of the original transaction, and if I'd just waited long enough it'd have eventually reset itself properly?

Thanks for the additional tips about NOLOGGING at the end.

Absolutely, you have it. :)

That is why theoretically, Oracle is impossible to corrupt. You can pull the power cord, crash the DB, whatever, as long as your redo logs are intact and your undo tablespace is intact, Oracle will recover the database back to a consistent state.