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

Did you bounce the database after creating another undo tablespace ? Check where the segs are :
  select tablespace_name,segment_name
  from dba_segments where segment_type='ROLLBACK'

and go from there. good luck,

   Roelof Schierbeek; DBA Received on Thu Nov 09 2006 - 02:26:54 CST