Undo Table Analysis

 

 

For Single Instance Database

 

1) Check parameter undo_management

    

    show parameter undo_management // If auto it means undo auto management is enabled

2) Name of undo tablespaces

    select tablespace_name,contents from dba_tablespaces where contents='UNDO';

 

3) Details of segment generated by users/schema

 

    Select owner,segment_name,tablespace_name from dba_rollback_Segs;

 

4) Detail of segment name and its status (Active/Expired/Unexpired)

 

    Select owner,segment_name,tablespace_name from dba_rollback_Segs;

 

5)  Detail of MB/Percent of Active,Expired & Unexpired segment

 

select status,round(sum_bytes / (1024*1024), 0) as MB,round((sum_bytes / undo_size) * 100, 0) as PERC from (select status, sum(bytes) sum_bytes from dba_undo_extents group by status),(select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE';

 

 6) Check the value of maxquerylen to suggest the value of undo_retnetion

 

           select max(maxquerylen) from v$undostat;
   

         7) Check the undo_retention type

    select  tablespace_name, retention from  dba_tablespaces; 

        
  For RAC as each instance has separate undo tablespace . The table name in the above query needs to be appeneded with G