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 :
drop tablespace UNDOTBS2
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS2’ is currently in use
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.
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,KTUXECFL Flags
FROM x$ktuxe WHERE ktuxesta!=’INACTIVE’;
SQL>select local_tran_id, state from dba_2pc_pending;
–local_tran_id is a trio combination of USN , Slot , Seq
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.
4,498 views last month, 26 views today