Shrinking undo tablespace

Useful links

Reclaiming Unused Space in Datafiles

Undo tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace:

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Undo segments in DBA_ROLLBACK_SEGS view

select owner, segment_name, tablespace_name, status
--select *
from dba_rollback_segs
 where 1 = 1
   --and tablespace_name='UNDOTBS1'
   --and status='ONLINE'
   --and segment_name ='_SYSSMU51_2438556163$'
;

Who holds UNDO tablespace

select  
   substr(a.os_user_name,1,15)   "OS User",
   substr(a.oracle_username,1,8) "DB User",
   substr(b.owner,1,8)           "Schema",
   substr(b.object_name,1,20)    "Object Name",
   substr(b.object_type,1,10)    "Type",
   substr(c.segment_name,1,15)   "RBS",
   substr(d.used_urec,1,12)      "# of Records",
   e.sid,
   e.serial#
from v$locked_object    a,
     dba_objects        b,
     dba_rollback_segs  c,
     v$transaction      d,
     v$session e
where  
   a.object_id =  b.object_id
 and
   a.xidusn    =  c.segment_id
and
   a.xidusn    =  d.xidusn
and
   a.xidslot   =  d.xidslot
and
   d.addr      =  e.taddr;

Issue: Drop UNDO including datafiles does not delete files at OS level

Find oracle process which hold file :

[root@hostname][/]:lsof | grep undo | grep deleted
oracle     3818     oracle  258u      REG               8,65 32421978112      17089 /u01/app/oracle/oradata/DB11G/undotbs01.dbf (deleted)

sqlplus / as sysdba
SQL> SELECT sid, serial#, process, terminal, program, event FROM v$session WHERE process in ('3818')

Look at this SID and it event, and find solution how to release hold of file by this session, for example I have issue with Restart QMon process