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 :

SQL> drop tablespace UNDOTBS2;
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.

 

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

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.