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

I got “ORA-30013: undo tablespace ‘UNDOTBS’ is currently in use ” error in Oracle database.

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

Details of error are as follows.

SQL> drop tablespace undotbs8 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS8' is currently in use

1. When trying to drop the undo tablespace , following error occurs :

2. You have verified the Note 341372.1 and does not apply.

3. Run the below queries . Output of one of the queries shows the transactions in ‘PREPARED’ state.

4. You have distributed transactions on your database.

The error is happening due to a failed commit of distributed transaction.
As a result of this pending distributed transaction , Undo Tablespace is not getting dropped.

DBA_UNDO_EXTENTS may not show the extents ‘Active’ as dba_2pc views being inconsistent with transaction table.

Purge or Remove the distributed transactions which are in ‘PREPARED’ state .

Depending on the output of the above queries from x$ktuxe and dba_2pc views, ask Oracle Global Software Support for assistance in using the steps of unpublished Document 401302.1 to remove the pending transaction.

Check dba_rollback_segs to list of online segments:

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

Find the SID, SERIAL#,INST_ID to kill session with ‘alter system kill session’ command as follows.

SQL> select SID, serial#,inst_id from v$transaction,dba_rollback_segs,gv$session 
     where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#,@INST_ID';

Try to drop the undo 8 tablespace again as follows.

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

Do you want to learn Oracle Database for Beginners, then read the following articles.