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
Leave a Reply