Monitor undo tablespace usage - Oracle Database Script. 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;
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;