Re: couldn't drop a rollback segment

From: <fitzjarrell_at_cox.net>
Date: 9 Nov 2006 08:41:30 -0800
Message-ID: <1163090489.984189.228160@e3g2000cwe.googlegroups.com>

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.

Single quotes denote text, double quotes surround object names:

SQL> drop rollback segment "_SYSSMU1$";

The leading _ causes the original 'invalid character' error; the "" surrounding the name allow it to be processed. Mixed case table names and other anomalies are allowed using such constructs:

SQL> create table MyFunnyName(blah number);

Table created.

SQL> create table "MyFunnyName" (blah_again number);

Table created.

SQL> select table_name
  2 from user_tables
  3 where table_name like 'M%'
  4 /

TABLE_NAME

---

MYFUNNYNAME
MyFunnyName

7 rows selected.

SQL> create table _mytest (testing varchar2(40)); create table _mytest (testing varchar2(40))

             *
ERROR at line 1:
ORA-00911: invalid character

SQL> create table "_MYTEST" (testing varchar2(40));

Table created.

SQL> Of course, to access these objects you also need to use "":

SQL> select * from _mytest;
select * from _mytest

              *
ERROR at line 1:
ORA-00911: invalid character

SQL> select * from "_MYTEST";

no rows selected

SQL> Single-quotes won't work:

SQL> select * from '_MYTEST';
select * from '_MYTEST'

              *
ERROR at line 1:
ORA-00903: invalid table name

SQL> The same error you received when you used single-quotes around your segment name.

David Fitzjarrell Received on Thu Nov 09 2006 - 10:41:30 CST