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

DalTXColtsFan
DalTXColtsFan used Ask the Experts™
on
I'm trying to drop the tablespace UNDOTBS1 but when I issue the command

drop tablespace UNDOTBS1 including contents and datafiles;

I get the error

ORA-01548: active rollback segment '_SYSSMU9$' found, terminate dropping tablespace
Cause: Tried to drop a tablespace that contains active rollback segment(s)

Action: Shutdown instances that use the active rollback segments in the tablespace and then drop the tablespace

I've tried shutting down the instance from within SQL*Plus but then I get an "ORACLE not available" error when I try to drop the tablespace.

Help?

Thanks
DTXCF
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

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


Top Expert 2009

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

Author

Commented:
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?

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
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 :)
Top Expert 2009

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

Author

Commented:
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?

Author

Commented:
"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"
Top Expert 2009

Commented:
>>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.
Top Expert 2009
Commented:
When loading large data like this, you may consider NOLOGGING options on the table until you've loaded it, that will reduce the REDO generated during the transaction. You can also use the APPEND SQL hint to use direct path.


1) alter table mytable nologging;

2) Disable any indexes on the table

3) In the insert do:

insert /* +append */ into mytable ...

4) Do the load.

5) alter table mytable logging;

6) enable indexes

7) Perform a full backup

Author

Commented:
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.
Top Expert 2009

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial