You might 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.
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' );
NAME STATUS USERNAME SID SERIAL# ---------- --------------- ----------- ---------- ---------- _SYSSMU691$ PENDING OFFLINE SCOTT 20 30
Now kill this sessions:
alter system kill session '20,30' immediate; system altered
Now try to drop the undo tablespace.
SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped.