Fetch Undo Usage

SET LINESIZE 150

SET PAGESIZE 50

COLUMN tablespace_name FORMAT A20

COLUMN file_name FORMAT A50

COLUMN size_in_mb FORMAT 9999999.99

COLUMN max_size_in_mb FORMAT 9999999.99

COLUMN used_mb FORMAT 9999999.99

SELECT

a.tablespace_name AS tablespace_name,

a.file_name AS file_name,

a.bytes / 1024 / 1024 AS size_in_mb,

a.maxbytes / 1024 / 1024 AS max_size_in_mb,

(SELECT SUM(bytes) / 1024 / 1024

FROM dba_undo_extents

WHERE tablespace_name = a.tablespace_name) AS used_mb

FROM

dba_data_files a

WHERE

a.tablespace_name = (SELECT value FROM v$parameter WHERE name = ‘undo_tablespace’)

ORDER BY

a.tablespace_name;

If you found this useful or have questions, share your thoughts in the comments below!

Author: Rohit Mishri