Fixing [ ORA 30013 ] overgrown undo tablespace.

SQL> show parameter undo

NAME              TYPE            VALUE

----------------- ------------------------

undo_tablespace   string          UNDOTBS1

 

SQL> create undo tablespace UNDOTBS2 datafile size 1G autoextend off;

Tablespace created.

SQL> alter system set UNDO_TABLESPACE=UNDOTBS2;

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

 

SQL> ho oerr ORA 30013

30013, 00000, "undo tablespace '%s' is currently in use"

// *Cause:   the specified undo tablespace is currently used by another instance.

// *Action:  Wait for the undo tablespace to become available or  change to another name and reissue the statement.

SQL>  select owner, segment_name, tablespace_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE' ;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME   STATUS

------ ------------------------------ ----------------- ----------------

PUBLIC _SYSSMU9_3945653786$           UNDOTBS1          ONLINE            

SQL>  select substr(USERNAME,1,10) USERNAME,SID,SERIAL#,OSUSER,TERMINAL,SEGMENT_NAME

from v$transaction,dba_rollback_segs,v$session where SADDR=SES_ADDR and XIDUSN=SEGMENT_ID;

USERNAME            SID    SERIAL# OSUSER     TERMINAL   SEGMENT_NAME

------------ ---------- ---------- ---------- ---------- ----------------------

CITIDEVCOL           21      13519 db1123     UNKNOWN    _SYSSMU9_3945653786$

Please confirm the kill session from the respective user , If there are many session you ll have to wait till the active queries complete and free the RBS from the old undo tablespace.  

SQL> alter system kill session '21,13519';

Session killed.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.