Home / APPS DBA / Undo Related Queries Part 2

Undo Related Queries Part 2

List of All active Transactions

SELECT sid,
username,
t.used_ublk,
t.used_urec
FROM   v$transaction t,
v$session s
WHERE  t.addr = s.taddr;

 

To list all Datafile of UndoTablespace

 

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%UNDO%’
OR tablespace_name LIKE ‘%RBS%’
ORDER  BY tablespace_name,
file_name;

 

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%UNDOTBS2%’
ORDER  BY tablespace_name,
file_name;

 

col file_name FOR a40
set pagesize 100
SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576    Mbytes,
maxbytes / 1048576 maxMbytes
FROM   dba_data_files
WHERE  tablespace_name LIKE ‘%APPS_UNDOTS1%’
ORDER  BY tablespace_name,
file_name;

 

SELECT file_name,
tablespace_name,
bytes / 1024 / 1024,
maxbytes / 1024 / 1024,
autoextensible
FROM   dba_data_files
WHERE  file_name LIKE ‘%undo%’
ORDER  BY file_name;

 

To check when a table is last analysed

 

SELECT owner,
table_name,
tablespace_name,
status,
last_analyzed,
partitioned,
dependencies,
dropped
FROM   dba_tables
WHERE  table_name LIKE ‘MLC_PICK_LOCKS_DETAIL’;

 

SELECT owner,
table_name,
tablespace_name,
last_analyzed,
partitioned,
dependencies,
dropped
FROM   dba_tables
WHERE  table_name LIKE ‘APPS.XLA_AEL_GL_V’;

 

To list all Undo datafiles with status and size

 

show parameter undo
show parameter db_block_size
col tablespace_name FORM a20
col file_name FORM a60
set lines 120
SELECT tablespace_name,
file_name,
status,
bytes / 1024 / 1024
FROM   dba_data_files
WHERE  tablespace_name = (SELECT tablespace_name
FROM   dba_tablespaces
WHERE  CONTENTS = ‘UNDO’);

 

Total undo space

SELECT SUM(bytes) / 1024 / 1024 / 1024 GB
FROM   dba_data_files
WHERE  tablespace_name = ‘&Undo_TB_Name’;

 

Undo Tablespace

 

SELECT tablespace_name
FROM   dba_tablespaces
WHERE  tablespace_name LIKE ‘%UNDO%’;

 

To find MaxQueryLength from stats$undostat

SELECT Max(maxquerylen)
FROM   stats$undostat;

SELECT Max(maxquerylen)
FROM   v$undostat;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date,
maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 11;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26
ORDER  BY begin_date ASC,
maxquerylen DESC;

SELECT begin_date,
u.maxquerylen
FROM   (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26
ORDER  BY maxquerylen DESC;

SELECT SUM(u.maxquerylen) / 25 AvgUndoRetTime
FROM   (SELECT maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26;

SELECT SUM(u.maxquerylen)
FROM   (SELECT maxquerylen
FROM   v$undostat
ORDER  BY maxquerylen DESC) u
WHERE  ROWNUM < 26;

 

DBA_UNDO_EXTENTS

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
GROUP  BY tablespace_name,
status
ORDER  BY tablespace_name;

 

Mapping Undo Segments to usernames

SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
( rs.rssize ) / 1024 / 1024 MB,
rn.name
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;

 

Total Undo Statistics

ALTER SESSION SET nls_date_format=‘dd-mon-yy hh24:mi’;

set lines 120
set pages 2000
SELECT begin_time,
end_time,
undoblks,
txncount,
maxquerylen,
unxpstealcnt,
expstealcnt,
ssolderrcnt,
nospaceerrcnt
FROM   v$undostat;

 

Total Undo Statistics since specified year

 

SELECT ‘TOTAL STATISTICS SINCE Jan 01, 2005 – STATSPACK’
FROM   dual;

set head ON
set lines 152
column undotsn format 999 heading ‘Undo|TS#’;
column undob format 9, 999, 999, 999 heading ‘Undo|Blocks’;
column txcnt format 9, 999, 999, 999, 999 heading ‘Num|Trans’;
column maxq format 999, 999 heading ‘Max Qry|Len (s)’;
column maxc format 9, 999, 999 heading ‘Max Tx|Concurcy’;
column snol format 9, 999 heading ‘Snapshot|Too Old’;
column nosp format 9, 999 heading ‘Out of|Space’;
column blkst format a13 heading ‘uS/uR/uU/|eS/eR/eU’ wrap;
column unst format 9, 999 heading ‘Unexp|Stolen’ newline;
column unrl format 9, 999 heading ‘Unexp|Relesd’;
column unru format 9, 999 heading ‘Unexp|Reused’;
column exst format 9, 999 heading ‘Exp|Stolen’;
column exrl format 9, 999 heading ‘Exp|Releas’;
column exru format 9, 999 heading ‘Exp|Reused’;
SELECT undotsn,
SUM(undoblks)        undob,
SUM(txncount)        txcnt,
Max(maxquerylen)     maxq,
Max(maxconcurrency)  maxc,
SUM(ssolderrcnt)     snol,
SUM(nospaceerrcnt)   nosp,
SUM(unxpstealcnt)
||‘/’
|| SUM(unxpblkrelcnt)
||‘/’
|| SUM(unxpblkreucnt)
||‘/’
|| SUM(expstealcnt)
||‘/’
|| SUM(expblkrelcnt)
||‘/’
|| SUM(expblkreucnt) blkst
FROM   stats$undostat
WHERE  dbid IN (SELECT dbid
FROM   v$database)
AND instance_number IN (SELECT instance_number
FROM   v$instance)
AND end_time > To_date(‘01012005 00:00:00’, ‘DDMMYYYY HH24:MI:SS’)
AND begin_time < (SELECT SYSDATE
FROM   dual)
GROUP  BY undotsn;

 

SELECT ( SUM(undoblks) ) / SUM (( end_time  begin_time ) * 86400)
FROM   v$undostat;

 

 

 

About Syed Saad Ali

As a certified and experienced Oracle Database Administrator with Almost 10 Years of Experience I am able to perform all levels of database maintenance and am fully qualified to do Oracle updates as well. Capitalizing on my interest in Business & IT both, I chose to navigate my career towards Enterprise Solutions, which has enabled me to excel in the combination of both. I have developed exceptional analytical skills that allow me to diagnose and take care of issues immediately. Extremely skilled in planning and execution of special projects in time- critical environments. Extensive knowledge in directing, coordinating and exercising concern authority for planning, organization, control and successful completions. Very skilled at solving customer relations issues by seeking first to understand and then to be understood. Effectively able to communicate with customers, vendors and management.

Check Also

JSP error Request URI:/OA_HTML/RF.jsp java.lang.NoClassDefFoundError

ERROR JSP Error: Request URI:/OA_HTML/RF.jsp Exception: java.lang.NoClassDefFoundError JSP Error: Request URI:/OA_HTML/AppsLocalLogin.jsp Exception: java.lang.NoClassDefFoundError More from …

Leave a Reply