Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Drop UNDO tablespace

Mercadante, Thomas F

2006-06-06

Replies:
Sami,

Have you bounced the database since you created the new UNDO tablespace?
It might clear out the old undo so that you can drop it.

Short of that, I would submit an SR to Oracle support and see what they
have to say.

Tom

-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Tuesday, June 06, 2006 9:46 AM
To: ORACLE-L
Subject: Drop UNDO tablespace

Env: Oracle 9.2.0.6, 3 Node RAC, Sun OS 2.9

We want to drop this undo tablespace UNDOTBS1 which is not part on any
instance.
But when we try to drop the tablespace it is throwing "ORA-01548:
active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace". Is there a way to overcome this?

The datafile for UNDOTBS1 segments got corrupted and in "NEEDS
RECOVERY" state for more than a month. We created new undo tablespace
(UNDOTBS1_NEW) that time and UNDOTBS1 tablespace not required any
more.

Alert_log
=======
Tue Jun 6 13:22:40 2006
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
.
.
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Tue Jun 6 13:22:41 2006
Errors in file /opt/oracle/admin/pwpb4/bdump/pwpb41_smon_26754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/oradata/pwpb4/undo01.dbf'


SQL> show parameter undo

NAME                      TYPE     VALUE
------------------------------------ -----------
------------------------------
undo_management               string    AUTO
undo_retention               integer   900
undo_suppress_errors           boolean   FALSE
undo_tablespace               string    UNDOTBS1_NEW

SQL>select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME             STATUS       TABLESPACE_NAME
------------------------------ ----------------
------------------------------
SYSTEM                 ONLINE       SYSTEM
_SYSSMU1$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU2$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU3$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU4$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU5$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU6$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU7$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU8$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU9$               NEEDS RECOVERY  UNDOTBS1
_SYSSMU10$              NEEDS RECOVERY  UNDOTBS1
_SYSSMU11$              ONLINE       UNDOTBS2
_SYSSMU12$              ONLINE       UNDOTBS2
_SYSSMU13$              ONLINE       UNDOTBS2
_SYSSMU14$              ONLINE       UNDOTBS2
_SYSSMU15$              ONLINE       UNDOTBS2
_SYSSMU16$              ONLINE       UNDOTBS2
_SYSSMU17$              ONLINE       UNDOTBS2
_SYSSMU18$              ONLINE       UNDOTBS2
_SYSSMU19$              ONLINE       UNDOTBS2
_SYSSMU20$              ONLINE       UNDOTBS2
_SYSSMU21$              ONLINE       UNDOTBS3
_SYSSMU22$              ONLINE       UNDOTBS3
_SYSSMU23$              ONLINE       UNDOTBS3
_SYSSMU24$              ONLINE       UNDOTBS3
_SYSSMU25$              ONLINE       UNDOTBS3
_SYSSMU26$              ONLINE       UNDOTBS3
_SYSSMU27$              ONLINE       UNDOTBS3
_SYSSMU28$              ONLINE       UNDOTBS3
_SYSSMU29$              ONLINE       UNDOTBS3
_SYSSMU30$              ONLINE       UNDOTBS3
_SYSSMU31$              ONLINE       UNDOTBS1_NEW
_SYSSMU32$              ONLINE       UNDOTBS1_NEW
_SYSSMU33$              ONLINE       UNDOTBS1_NEW
_SYSSMU34$              ONLINE       UNDOTBS1_NEW
_SYSSMU35$              ONLINE       UNDOTBS1_NEW
_SYSSMU36$              ONLINE       UNDOTBS1_NEW
_SYSSMU37$              ONLINE       UNDOTBS1_NEW
_SYSSMU38$              ONLINE       UNDOTBS1_NEW
_SYSSMU39$              ONLINE       UNDOTBS1_NEW
_SYSSMU40$              ONLINE       UNDOTBS1_NEW

41 rows selected.

SQL> select file_name,TABLESPACE_NAME,status from dba_data_files
where TABLESPACE_NAME like 'UNDO%'
2 ;

FILE_NAME                     TABLESPACE_NAME
STATUS
----------------------------------------
------------------------------ ---------
/u06/oradata/pwpb4/undo01.dbf        UNDOTBS1
AVAILABLE
/u06/oradata/pwpb4/undo02.dbf        UNDOTBS2
AVAILABLE
/u06/oradata/pwpb4/undo03.dbf        UNDOTBS3
AVAILABLE
/u06/oradata/pwpb4/undo01_new.dbf     UNDOTBS1_NEW
AVAILABLE

SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l