|
#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
|
