ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA error message itself is self explanatory, When undo tablespace is in use and we will try to drop that tablespace than oracle pop up with ORA-30013 error.

Solution:
Wait until active transactions are COMMITED or ROLLED BACK. If so, you can easily drop mentioned tablespace.
OR
You can kill that transaction if it unnecessary.

Consider following example:

SQL> create tablespace newundotbs datafile '/u01/app/oracle/oradata/prod/newundotbs1.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.

SQL> alter system set UNDO_TABLESPACE=NEWUNDOTBS scope=spfile;
System altered.

Following command will failed because there is active transactions.

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

Query dba_rollback_segs to know list of online segments:

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

Retrive SID and SERIAL# from below mentioned query in order to kill session with ‘alter system kill session’ command.

SQL> select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#';

SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

Tablespace successfully dropped.

Thanks
Stay Tune 🙂