Gather all undo information using a single script

 

In our daily life, sometimes we require to gather various information related to undo tablespace and for many of us it becomes challenging to remember all the views to gather that information.

 

Using this script, we can gather various information related to undo tablespace in a single go.

 

[db@server102 ~]$ cat undotablespace.sql

#Please execute following script to generate html file   set markup html on spool on SPOOL UNDO_INFO.HTML set pagesize 200 set echo on;   select * from v$version; show parameter undo alter session set nls_date_format='DD-MON-YY HH:MI:SS AM'; select * from v$database; select * from gv$instance;   select inst_id,sid,name,value from gv$spparameter where name like '%undo%';   select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value", a.KSPPDESC "Describtion" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '%smu%';   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','_rollback_segment_count' ) ) order by 1;   select max(maxquerylen), max(tuned_undoretention) , max(undoblks), avg (undoblks), avg(maxquerylen), avg(tuned_undoretention) from v$undostat;   select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;   select status, round(sum(bytes)/(1024*1024)) size_mb, count(status) number_of_ext from dba_undo_extents group by status;   select tablespace_name,status,count(*) from dba_rollback_segs group by status,tablespace_name order by 1;   select object_name, reason from dba_outstanding_alerts;   SELECT tablespace_name, status,round(sum(bytes)/(1024*1024)) size_m, COUNT (*) FROM SYS.dba_undo_extents GROUP BY tablespace_name, status order by tablespace_name,status;   select to_char(begin_time, 'DD-MON-YYYY HH24:MI:SS') begin_time, tuned_undoretention from v$undostat;   select to_char(begin_time, 'DD-MON-YYYY HH24:MI:SS') begin_time, to_char(end_time, 'DD-MON-YYYY HH24:MI:SS') end_time, undotsn, undoblks, txncount, maxconcurrency as "MAXCON", maxquerylen, tuned_undoretention from v$undostat order by 1;   select * from v$undostat ; select * from gv$undostat;   column UNXPSTEALCNT heading "# Unexpired-Stolen" column EXPSTEALCNT heading "# Expired-Reused" column SSOLDERRCNT heading "ORA-1555" column NOSPACEERRCNT heading "Out-Of-space" column MAXQUERYLEN heading "Max Query Length"   select inst_id, to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') "Begin Time", unxpstealcnt, expstealcnt , ssolderrcnt, nospaceerrcnt, maxquerylen, tuned_undoretention "Tuned Undo" from gv$undostat where SSOLDERRCNT > 0 order by inst_id, begin_time;     select tablespace_name,block_size,status,contents,retention,extent_management,segment_space_management,status,bigfile from dba_tablespaces where contents='UNDO' order by contents,tablespace_name;   select tablespace_name,file_name,round(bytes/1024/1024) "SIZE (MB)",autoextensible,round(maxbytes/1024/1024) "MAX SIZE (MB)", status from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where contents='UNDO') order by tablespace_name,file_name;   SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where CONTENTS='UNDO') GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes /   SELECT s.sid, s.serial#, s.username, u.segment_name, count(u.extent_id) "Extent Count", t.used_ublk, t.used_urec, s.program FROM v$session s, v$transaction t, dba_undo_extents u WHERE s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE' GROUP BY s.sid, s.serial#, s.username, u.segment_name, t.used_ublk, t.used_urec, s.program ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;   select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq from x$ktuxe a, undo$ b where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%' and a.ktuxeusn = b.us#;       spool off set markup html off spool off

 

Execute the sql

[db@server102 ~]$ sqlplus / as sysdba   SQL> @undotablespace.sql