- 08 Dec, 2023
- 0 Comments
- 45 Secs Read
ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use
You may get this error, when you tried to drop the undo table. Its because , there are some active transactions in the undo.
The solution is to find that transaction and kill the same.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | set pagesize 200set lines 200set long 999col username for a9SELECT a.name,b.status , d.username , d.sid , d.serial#FROM v$rollname a,v$rollstat b, v$transaction c , v$session dWHERE a.usn = b.usnAND a.usn = c.xidusnAND c.ses_addr = d.saddrAND a.name IN (SELECT segment_nameFROM dba_segmentsWHERE tablespace_name = 'UNDOTBS1');NAME STATUS USERNAME SID SERIAL#---------- --------------- ----------- ---------- ----------_SYSSMU691$ PENDING OFFLINE SCOTT 20 30 |
1 2 3 | alter system kill session '20,30' immediate;system altered |
1 2 | SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped. |