undotbs

 with undotbs as (
select inst_id, max(decode(name,'undo_retention',undovalue)) Retention , max(decode(name,'undo_tablespace',undovalue)) undotbs
from (
select inst_id,name, decode(name,'undo_retention',value ||'s - ',value) undovalue
from gv$parameter
where name in ( 'undo_tablespace','undo_retention','undo_management', '_smu_debug_mode','_highthreshold_undoretention') ) group by inst_id ), undo_gurantee as (
SELECT b.inst_id, decode(retention,'GUARANTEE','YES','NO') undo_gurantee
FROM dba_tablespaces a, gv$parameter b
where a.tablespace_name = upper(b.value) and b.name ='undo_tablespace' ), fast_recover as (
select inst_id, sum(undoblockstotal-undoblocksdone) * 8192 /(1048576) recoverydue
from gv$fast_start_transactions group by inst_id ) , rollback as (
select inst_id, sum(used_ublk)*8192/(1048576*1000) rollbackrec
from gv$transaction group by inst_id ), undofree as (
select tablespace_name, round(sum(bytes)/(1048576*1000),2) GBFree
from dba_free_space a, undotbs b
where lower(a.tablespace_name) = lower(b.undotbs) group by tablespace_name ), undoavl as (
select tablespace_name, round(sum(bytes)/(1048576*1000),2) GBavl
from dba_data_files a, undotbs b
where lower(tablespace_name) = lower(b.undotbs) group by tablespace_name ), undostatus as (
select tablespace_name, max(decode(status,'UNEXPIRED',MBytes)) UnExpired, max(decode(status,'EXPIRED',MBytes)) Expired, max(decode(status,'ACTIVE',MBytes)) Active
from (
SELECT tablespace_name, STATUS, round(SUM(BYTES)/(1048576*1000),0) MBytes , COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS ) group by tablespace_name ), undoshort as (
SELECT inst_id , decode( avg(UNXPSTEALCNT)+ avg( EXPBLKREUCNT) +avg(NOSPACEERRCNT),0,null,'YES') undoshort
FROM gv$UNDOSTAT
where ( UNXPSTEALCNT !=0 or EXPBLKREUCNT !=0 or NOSPACEERRCNT !=0 or UNEXPIREDBLKS >0 ) group by inst_id ) , undomax as (
select inst_id, max(TUNED_UNDORETENTION) TunedRet, max(MAXQUERYLEN) MaxQryLen, sum(SSOLDERRCNT ) ORA155ER
from gv$undostat
where end_time > sysdate - 1 group by inst_id )
select
--a.inst_id,
sys_context('userenv','DB_NAME') ||decode( 0,1,'-'||a.inst_id) db_name , a.undotbs , Retention || undo_gurantee Retention, round(c.GBAvl ) "TotSize", round(b.GBFree) "FreeGB" , round ( ( decode(b.GBFree,0,1,b.GBFree)/c.GBAvl ) * 100) "Free %" , expired , unexpired, active ,
--undoshort ,
rOLLBACKREC, recoverydue RECOVERYGB, maxqrylen, TunedRet, ora155er, CASE WHEN ( recoverydue > 0 ) then '*SMON Recovering*' WHEN ( rollbackrec > 0.5 ) then '* Large Rollback*' WHEN ( (unexpired / gbavl) * 100 > 20 or unexpired > 50 ) then '* Large Unexpired undo' WHEN round( ( decode(b.GBFree,0,1,b.GBFree)/c.GBAvl ) * 100) < 30 then '*Low UndoSpace*' ELSE ' ' end "Warning"
from undotbs a, undofree b, undoavl c, undostatus d, undoshort e, rollback f, fast_recover g, undomax h, undo_gurantee ug
where lower(a.undotbs) = lower(b.tablespace_name(+)) and lower(a.undotbs) = lower(c.tablespace_name(+)) and lower(a.undotbs) = lower(d.tablespace_name(+)) and a.inst_id = e.inst_id(+) and a.inst_id = f.inst_id (+) and a.inst_id = g.inst_id (+) and a.inst_id = h.inst_id (+) and a.inst_id = ug.inst_id (+) order by a.inst_id