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 |
