Re: couldn't drop a rollback segment

Yes we bounced the database after creating the new undo tablespace.

The rollback segs are from the old undo tablespace UNDOTBS02:

SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
Press RETURN to continue

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------
----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU2$                      UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU3$                      UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU4$                      UNDOTBS01                      ONLINE
_SYSSMU5$                      UNDOTBS01                      ONLINE
_SYSSMU6$                      UNDOTBS01                      ONLINE
_SYSSMU7$                      UNDOTBS01                      ONLINE
_SYSSMU8$                      UNDOTBS01                      ONLINE
_SYSSMU9$                      UNDOTBS01                      ONLINE
_SYSSMU10$                     UNDOTBS01                      ONLINE
_SYSSMU11$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU12$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU13$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU14$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU15$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU16$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU17$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU18$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU19$                     UNDOTBS02                      NEEDS
RECOVERY
_SYSSMU20$                     UNDOTBS02                      OFFLINE
_SYSSMU21$                     UNDOTBS01                      ONLINE
_SYSSMU22$                     UNDOTBS01                      ONLINE
_SYSSMU23$                     UNDOTBS01                      ONLINE

The datafile for UNDOTBS02 no longer exists, and we don't have any backup.
How can we force it to drop UNDOTBS02 without doing any recovery?

Thanks.

On Nov 9, 3:26 am, "R. Schierbeek" <byteNos..._at_gmail.com> wrote:
> <siuh..._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- Hide quoted text -- Show quoted text -
Received on Thu Nov 09 2006 - 08:17:07 CST