Check the Undo tablespace Usage in Oracle

Check the Undo tablespace total, free and used space usage in Oracle

Check the undo tablespace total, free and used space(Size in MB) in Oracle

SELECT a.tablespace_name,
SIZEMB,
USAGEMB,
(SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME         SIZEMB    USAGEMB     FREEMB
------------------- ---------- ---------- ----------
UNDOTBS1                    65      1.625     63.375

Check the Active, expired and unexpired transaction space usage in Undo Tablespace
ACTIVE: Status show us the active transaction going in database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status show us the transaction which is completed and complete the undo_retention time and now first candidate for trucated from undo tablespace.
UNEXPIRED: Status show us the transaction which is completed but not completed the undo retention time. It can be trucated if required.

select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts
from dba_undo_extents
group by tablespace_name, status order by 1,2;

TABLESPACE             STATUS  SUM_IN_MB     COUNTS
------------------- --------- ---------- ----------
UNDOTBS1	           ACTIVE       1.00          1
UNDOTBS1              EXPIRED     2.3125         22
UNDOTBS1            UNEXPIRED      1.625         11

Check undo usage by User or schema

select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts
from dba_undo_extents u, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;


Author: SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator. View all posts by SandeepSingh