Check free space in UNDO Tablespace

      No Comments on Check free space in UNDO Tablespace

How to check free space in UNDO tablespace

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 = '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 184320 403.125 183916.875
UNDOTBS2 194560 163970.688 30589.3125

Leave a Reply

Your email address will not be published. Required fields are marked *