ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use

• You may encounter this error when attempting to drop the undo table. This occurs because there are active transactions within the undo, preventing its removal.
  
  
• Check which session is currently using UNDO.
  

set pagesize 200
set lines 200
set long 999
col username for a9
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);

• Terminate the session.
  

alter system kill session 'sid,serial#' immediate;

• Drop the UNDO tablespace.

drop tablespace undotbs1 including contents and datafiles;