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.
