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