check undo info

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col status format a15
col description format a40 word_wrap
set serverout on size 999999 lines 150 verify off pages 50 echo off trimspool on
break on report
compute Sum LABEL SUM of undosize_MB on report
compute Sum LABEL SUM of block_count on report

col undo_alloc new_value undo_alloc  noprint
col undo_pct format 9999
spool undo_info.log
SELECT SUM(bytes)/1024/1024  undo_alloc
FROM    DBA_data_files
WHERE   tablespace_name =
        (SELECT UPPER(value)
        FROM    v$parameter
        WHERE   name    =       'undo_tablespace');

ttitle left   '*********** REPORT 1 - Undo Block Status and Free Space Analysis ********************' skip 2

col Description format a40 word_wrap
col undo_pct format a5 head "UNDO|PCTGE"
SELECT  status,
        DECODE(status,'ACTIVE','UNDO BEING USED','EXPIRED','UNDO AVAILABLE FOR USE','UNEXPIRED','UNDO BEING RETAINED TO SUPPORT UNDO RETENTION. CAN BECOME EXPIRED IF SPACE BECOMES TIGHT') Description,
        count(*) block_count,
        round(sum(bytes)/1024/1024)  undosize_MB,
        round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%' undo_pct
FROM    dba_undo_extents
GROUP BY status
UNION
SELECT  'FREE SPACE',
        'UNDO FREE SPACE AVAILABLE FOR USE',
        COUNT(*),
        ROUND(SUM(bytes)/1024/1024),
        round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%'
FROM    dba_free_space
WHERE   tablespace_name   =
        (SELECT value
        FROM    v$parameter
        WHERE   name    =       'undo_tablespace')
GROUP BY 1
/

ttitle off
ttitle left '*********** REPORT 2 - Undo Health Check ********************' skip 2
DECLARE
    tablespaceName        varchar2(30);
    tablespaceSize        number;
    autoExtend            boolean;
    autoExtendtf          char(5);
    undoRetention         number;
    retentionGuarantee    boolean;
    retentionGuaranteetf  char(5);
    autotuneEnabled       boolean;
    autotuneEnabledtf     char(5);
    longestQuery          number;
    longestQueryFormatted varchar2(30);
    requiredRetention     number;
    requiredRetentionFormatted varchar2(20);
    bestPossibleRetention number;
    bestPossibleRetentionFormatted varchar2(20);
    requireUndoSize       number;
    --
    problem               varchar2(100);
    recommendation        varchar2(100);
    rationale             varchar2(100);
    retention             number;
    utbsize               number;
    nbr                   number;
    undoAdvisor           varchar2(100);
    instanceNumber        number;
    ret                   boolean;
    rettf                 char(5);
    undoRetentionFormatted varchar2(50);
    Recommended_undo_size number;
    --
--
BEGIN

   ret := sys.dbms_undo_adv.undo_info (tableSpaceName, tableSpaceSize, autoExtend, undoRetention, retentionGuarantee);

   if ret
   then rettf := 'TRUE';
   else rettf := 'FALSE';
   end if;

   if autoextend
   then autoextendtf := 'TRUE';
   else autoextendtf := 'FALSE';
   end if;

   if retentionguarantee
   then retentionguaranteetf := 'TRUE';
   else retentionguaranteetf := 'FALSE';
   end if;

   SELECT to_char(trunc( max(undoRetention)/(60*60))||' hrs ')
    || trunc(to_char( ( max(undoRetention) - (3600 * trunc(max(undoRetention)/3600) )  )/60)) ||' mins '
    INTO undoRetentionFormatted
   FROM Dual;

   longestquery := dbms_undo_adv.longest_query(sysdate-1,sysdate);
    SELECT to_char(trunc( max(longestQuery)/(60*60))||' hrs ')
    || trunc(to_char( ( max(longestQuery) - (3600 * trunc(max(longestQuery)/3600) )  )/60)) ||' mins '
    INTO longestQueryFormatted
   FROM Dual;

--   dbms_output.put_line(' ');
--   dbms_output.put_line('--------------------------------------------------');
--   dbms_output.put_line('*                     UNDO Health                *');
--   dbms_output.put_line
 dbms_output.put_line( '*********** REPORT 2 - Undo Health Check ********************');
 dbms_output.put_line(' ');
 dbms_output.put_line(' ');
   nbr := dbms_undo_adv.undo_health (problem, recommendation, rationale, retention, utbsize);

    SELECT DECODE(utbsize,0,tableSpaceSize,utbsize)
    INTO Recommended_undo_size
    FROM dual;

   dbms_output.put_line (RPAD('Problem',35,CHR(0))||' : '||problem);
   dbms_output.put_line (RPAD('Recommendation',35,CHR(0))||' : '||recommendation);
   dbms_output.put_line (RPAD('Rationale',35,CHR(0))||' : '||rationale);
   dbms_output.put_line (RPAD('undo_retention (secs)',35,CHR(0))||' : '||undoRetention);
   dbms_output.put_line (RPAD('undo_retention (hrs/mins)',35,CHR(0))||' : '||undoRetentionFormatted);
   dbms_output.put_line (RPAD('Guaranteed Retention',35,CHR(0))||' : '||retentionGuaranteetf);
   dbms_output.put_line (RPAD('Longest Run Query (secs)',35,CHR(0))||' : '||longestQuery );
   dbms_output.put_line (RPAD('Longest Run Query (hrs/mins)',35,CHR(0))||' : '||longestQueryFormatted );
  dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||Recommended_undo_size);
--  dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||utbsize);
   dbms_output.put_line (RPAD('Current Undo T/S Size (MB)',35,CHR(0))||' : '||tableSpaceSize);

END;
/

col SSOLDERRCNT format 999999999 HEAD "SNAPSHOT|TOO OLD|ERROR|COUNT"
col NOSPACEERRCNT format 9999999 HEAD "NOSPACE|ERROR|COUNT"   
ttitle off
ttitle left '*********** REPORT 3 - Current Undo Stats ********************' skip 2

SELECT BEGIN_TIME,
 END_TIME,
 UNDOBLKS,
 MAXQUERYLEN,
 MAXQUERYID,
 SSOLDERRCNT,
 NOSPACEERRCNT,
 TUNED_UNDORETENTION
FROM v$undostat
WHERE BEGIN_TIME> sysdate-.090
ORDER BY 1;
ttitle off
ttitle left '*********** REPORT 4 - Undo Datafiles  ********************' skip 2
col filename format a65
col TSPACENAME      format a12
col CURRENT_SIZE_MB format 999,999 head 'CURRENT|SIZE(MB)'
col AUTOEXTEND_UP_TO_SIZE format 9,999,999 head 'AUTOEXTEND UP|TO SIZE(MB) '

SELECT  d.file_name "FILENAME",
        d.bytes/1024/1024 CURRENT_SIZE_MB,
        d.maxbytes/1024/1024 AUTOEXTEND_UP_TO_SIZE,
        t.tablespace_name "TSPACENAME",
        CASE
        WHEN    d.autoextensible='YES' AND d.bytes>=d.maxbytes   THEN    'WORKROUND IN PLACE'
        WHEN    d.autoextensible='YES' AND d.bytes<d.maxbytes   THEN    'CAN AUTOEXTEND'
        WHEN    d.autoextensible='NO'   THEN    'NO AUTOEXTEND'
        END AUTOEXTEND
FROM    dba_data_files      d,
        dba_tablespaces    t,
        v$parameter     p
WHERE   d.tablespace_name   =       t.tablespace_name
AND     d.tablespace_name   =   UPPER(p.value)
AND     p.name  =       'undo_tablespace'
/
ttitle off

ttitle left '*************** REPORT 5 - Current Undo Activity **************' skip 2
col dummy noprint
SELECT * from dual;

col userdet     heading "OSUSER : |USERNAME"       format A15
col procid      heading "SID:SERIAL - |SPID"       format A15
col terminal    heading "TTY#"                  format A15
col program     heading "PROGRAM NAME"          format A20
col status      heading "STATUS"                format A10
col name        heading "UNDO|SEGMENT"          format a15
col sql_text    heading "CURRENT SQL STATEMENT" format a100 word_wrap
col used_ublk   heading "USED|UNDO"             format a10
col start_date  heading "START DATE"
SELECT  unique
        RPAD(vs.osuser,13,' ')||': '|| vs.username userdet,
        RPAD(vs.sid||':'|| vs.serial#,13,' ') ||'- '|| vp.spid procid,
--        vs.terminal ,
        vs.program ,
        vs.status ,
        vr.name ,
        vt.used_ublk * TO_NUMBER(ts.block_size)/1024||'K' used_ublk,
        vt.start_date ,
        vsql.sql_text
FROM    v$rollname vr,
        v$transaction vt,
        v$sql vsql,
        v$process vp,
        v$session vs,
        (
        SELECT  dt.block_size
        FROM    v$parameter vp,
                dba_tablespaces dt
        WHERE   vp.value        =       dt.tablespace_name
        AND     vp.name          =      'undo_tablespace'
        ) ts
WHERE   vs.paddr = vp.addr
-- AND     NVL(vs.sql_id,vs.prev_sql_id) = vsql.sql_id
AND     vs.sql_id = vsql.sql_id(+)
AND     vs.taddr = vt.addr
AND     vt.xidusn = vr.usn;
ttitle off

spool off