Re: couldn't drop a rollback segment

siuhaic_at_gmail.com wrote:
> Hello,
>
> We lost a datafile belonging to an undo tablespace. Since we are in the
> process of setting up the test environment, we don't have any backup of
> the undo datafile. So we created another undo tablespace and set
> undo_tablespace instance parameter to point to the new one.
>
> When we were trying to drop the old undo tablespace, we received the
> following error:
>
> SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf'
> offline drop;
>
> Database altered.
>
> SQL> drop tablespace undotbs02;
> drop tablespace undotbs02
> *
> ERROR at line 1:
> ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
> dropping
> tablespace
>
>
> SQL> drop rollback segment _SYSSMU1$;
> drop rollback segment _SYSSMU1$
> *
> ERROR at line 1:
> ORA-00911: invalid character
>
> SQL> drop rollback segment '_SYSSMU1$';
> drop rollback segment '_SYSSMU1$'
> *
> ERROR at line 1:
> ORA-02175: invalid rollback segment name
>
> What should we do in order to drop undo tablespace UNDOTBS02? Since
> this is a test box, we don't really care about data loss.
>
> Thanks for your help.

Is this production?

If all transactions were closed there is an underscore (undocumented) parameter that support can provide to open the database and ignore bad undo files/blocks.

The database will open and you can then drop the bad tablespace. Received on Thu Nov 09 2006 - 08:41:26 CST