Re: Force drop of UNDO tablespace (with online segment) in

From: Mladen Gogala <>
Date: Tue, 22 Sep 2009 06:51:54 +0000 (UTC)
Message-ID: <h99s6a$9pj$>

Na Mon, 21 Sep 2009 20:52:32 -0700, DG problem napisao:

> I'll probably just restart the import process again, but I was wondering
> if there is any way to drop UNDOTBS1?

Yes, there is. In the error message (ORA-01548) there is also the name of the rollback segment. The message looks like this:

SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles *
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11$' found, terminate dropping tablespace

Here is the solution:

startup mount;

alter system set undo_management = 'MANUAL' scope=spfile;

alter system set "_corrupted_rollback_segments"="_SYSSMU11$" scope=spfile;


drop rollback segment "_SYSSMU11$";


startup nomount
alter database open

That was extracted from the ML note 28814.1. Of course, you should return to the automatic undo afterwards.

Received on Tue Sep 22 2009 - 01:51:54 CDT

Original text of this message