List All Undo Datafiles With Status And Size

show parameter undo

show parameter db_block_size

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

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

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

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’);

To Check When a Table is Last Analysed

List All Datafile Of Undo Tablespace

Undo Space Used By All Transactions

List Of Long Running Queries

Undo Tablespace Utilization By All Sessions

To Check For Active Transactions

Datewise Occurrence Of ORA-1555

To Check Space Related Statistics Of Undo Tablespace

To Show Undo Retention Value

Extent Count And Total Blocks

To Show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace

To Check Retention Guarantee For Undo Tablespace