set pagesize 200 
SET markup HTML on 
spool Undo_Check.html 
set echo on 
show parameter undo 
/* Required Space */ 
SELECT (UR * (UPS * DBS))/1024/1024/1024 AS "G_Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace')); 
 SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024/1024 as "G_Bytes", COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS; 
/* Peak Undo generation */ 
SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat); 
/* Undo tablespace size */ 
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'); 
select sum(bytes/1024/1024) MB_FREE from dba_free_space where tablespace_name= (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'); 
/* Max Query Length */ 
select max(maxquerylen) from v$undostat; 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Internal AUM settings */ 
select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter' ) ) order by 1; 
/* Tuned Undo Retention */ 
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(SSOLDERRCNT) FROM V$UNDOSTAT; 
column UNXPSTEALCNT heading "# Unexpired|attempt" 
column EXPSTEALCNT heading "# Expired|attempt" 
column SSOLDERRCNT heading "ORA-1555|Error" 
column NOSPACEERRCNT heading "Out-Of-space|Error" 
column MAXQUERYLEN heading "Max Query|Length" 
column ACTIVEBLKS heading "Active Blocks" 
column UNEXPIREDBLKS heading "Unexpired Blocks" 
column EXPIREDBLKS heading "Expired Blocks" 
column TUNED_UNDORETENTION heading "Tuned Undo retention" 
column UNXPBLKRELCNT heading "Unexpired|Removed for reuse" 
column UNXPBLKREUCNT heading "Unexpired|Reused by Transactions" 
column EXPBLKRELCNT heading "Expired| Stolen from other" 
column EXPBLKREUCNT heading "Expired| Stolen from same" 
column UNDOBLKS heading "Undo Blocks" 
select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS,UNXPBLKRELCNT,UNXPBLKREUCNT,EXPBLKRELCNT,EXPBLKREUCNT,MAXQUERYLEN, TUNED_UNDORETENTION from gv$undostat order by inst_id, begin_time; 
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO'; 
select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs; 
select t.start_time, t.used_ublk, s.username, r.segment_name.r.tablespace_name from v$transaction t,v$session s, dba_rollback_segs r where t.ses_addr= s.saddr and t.xidusn=r.segment_id; 
 SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",t1.tablespace_name FROM SYS.v_$rollname r,SYS.v_$session s,SYS.v_$transaction t,SYS.v_$parameter x,dba_rollback_segs t1 WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.NAME = 'db_block_size' AND t1.segment_id = r.usn AND t1.tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'); 
select dtxn.ktuxeusn, dtxn.ktuxeslt, dtxn.ktuxesqn, dtxn.ktuxesta,dtxn.ktuxesiz from x$ktuxe dtxn where dtxn.ktuxesta <> 'INACTIVE' and dtxn.ktuxecfl like '%DEAD%' order by dtxn.ktuxesiz asc; 
Select count(*) from dba_outstanding_alerts; 
select object_name, reason from dba_outstanding_alerts; 
select CREATION_TIME,METRIC_VALUE, reason, suggested_action from DBA_ALERT_HISTORY where OBJECT_NAME = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'); 
spool off 
set markup html off