Undo Related Queries Part 1

To check Retention Guarantee for Undo Tablespace

SELECT tablespace_name,
status,
CONTENTS,
logging,
retention
FROM   dba_tablespaces
WHERE  tablespace_name LIKE ‘%UNDO%’;

To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace

SELECT tablespace_name,
status,
Count(extent_id)                  “Extent Count”,
SUM(blocks)                       “Total Blocks”,
SUM(blocks) * 8 / ( 1024 * 1024 ) total_space
FROM   dba_undo_extents
GROUP  BY tablespace_name,
status;

Extent Count and Total Blocks

set linesize 152
col tablespace_name FOR a20
col status FOR a10
SELECT tablespace_name,
status,
Count(extent_id)                    “Extent Count”,
SUM(blocks)                         “Total Blocks”,
SUM(bytes) / ( 1024 * 1024 * 1024 ) spaceInGB
FROM   dba_undo_extents
WHERE  tablespace_name IN ( ‘&undotbsp’ )
GROUP  BY tablespace_name,
status;

To show UndoRetention Value

Show parameter undo_retention;

Undo retention in hours

col “Retention” FOR a30
col name FOR a30
col value FOR a50
SELECT name            “Retention”,
value / 60 / 60 “Hours”
FROM   v$parameter
WHERE  name LIKE ‘%undo_retention%’;

To check space related statistics of  UndoTablespace from stats$undostat of 90 days

SELECT undoblks,
begin_time,
maxquerylen,
unxpstealcnt,
expstealcnt,
nospaceerrcnt
FROM   stats$undostat
WHERE  begin_time BETWEEN SYSDATE – 90 AND SYSDATE
AND unxpstealcnt > 0;

To check space related statistics of  UndoTablespace from v$undostat

select sum(ssolderrcnt) “Total ORA-1555s”, round(max(maxquerylen)/60/60) “Max Query HRS”, SUM(unxpstealcnt) “UNExpired STEALS”, SUM(expstealcnt) “Expired STEALS” FROM v$undostat ORDER BY begin_time;

Date wise occurrence of ORA-1555

SELECT To_char(begin_time, ‘mm/dd/yyyy hh24:mi’) “Int. Start”,
ssolderrcnt                               “ORA-1555s”,
maxquerylen                               “Max Query”,
unxpstealcnt                              “UNExp SCnt”,
unxpblkrelcnt                             “UnEXPblks”,
expstealcnt                               “Exp SCnt”,
expblkrelcnt                              “ExpBlks”,
nospaceerrcnt                             nospace
FROM   v$undostat
WHERE  ssolderrcnt > 0
ORDER  BY begin_time;

Total number of ORA-1555s since instance startup

SELECT ‘TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : ‘
|| To_char(startup_time, ‘DD-MON-YY HH24:MI:SS’)
FROM   v$instance;

To check for Active Transactions

set head ON
SELECT usn,
extents,
Round(rssize / 1048576) rssize,
hwmsize,
xacts,
waits,
optsize / 1048576       optsize,
shrinks,
wraps
FROM   v$rollstat
WHERE  xacts > 0
ORDER  BY rssize;

Undo Space Utilization by each Sessions

set lines 200
col sid FOR 99999
col username FOR a10
col name FOR a15
SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
rn.name,
( t.used_ublk * 8 ) / 1024 / 1024 SizeGB
FROM   v$transaction t,
v$session s,
v$rollstat rs,
v$rollname rn
WHERE  t.addr = s.taddr
AND rs.usn = rn.usn
AND rs.usn = t.xidusn
AND rs.xacts > 0;

List of long running queries since instance startup

set head OFF
SELECT ‘LIST OF LONG RUNNING – QUERY SINCE INSTANCE STARTUP’
FROM   dual;

set head ON
SELECT *
FROM   (SELECT To_char(begin_time, ‘DD-MON-YY hh24:mi:ss’) BEGIN_TIME,
Round(( maxquerylen / 3600 ), 1)            Hours
FROM   v$undostat
ORDER  BY maxquerylen DESC)
WHERE  ROWNUM < 11;

Undo Space used by all transactions

set lines 200
col sid FOR 99999
col username FOR a10
col name FOR a15
SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
rn.name,
( t.used_ublk * 8 ) / 1024 / 1024 SizeGB
FROM   v$transaction t,
v$session s,
v$rollstat rs,
v$rollname rn
WHERE  t.addr = s.taddr
AND rs.usn = rn.usn
AND rs.usn = t.xidusn
AND rs.xacts > 0;