How to get an overview of the undo tablespace usage, including total size and free space

 

Checking Undo Tablespace Usage

To get an overview of the undo tablespace usage, including total size and free space:

SELECT tablespace_name, df.file_id, file_name, bytes / 1024 / 1024 AS total_size_mb, (bytes - NVL(free_space, 0)) / 1024 / 1024 AS used_space_mb, NVL(free_space, 0) / 1024 / 1024 AS free_space_mb FROM (SELECT file_id, file_name, tablespace_name, bytes FROM dba_data_files WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')) df LEFT JOIN (SELECT file_id, SUM(bytes) AS free_space FROM dba_free_space GROUP BY file_id) fs ON df.file_id = fs.file_id;

Here is the Output