UNDO Tablespace Usage

I use below query to find UNDO tablespace usage:

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;

Temporary Tablespace Usage

I use below query to find temporary tablespace usage:


select b.tablespace_name,
b.SizeMB,
nvl(a.UsageMB, 0) as UsageMB,
b.SizeMB - nvl(a.UsageMB, 0) as FreeMB
from (select sum(BLOCKS) * b.VALUE / 1024 / 1024 as UsageMB,
tablespace as tablespace_name
from v$tempseg_usage a, v$parameter b
where b.name = 'db_block_size'
group by TABLESPACE, b.VALUE) a,
(select sum(bytes) / 1024 / 1024 as SizeMB, tablespace_name
from dba_temp_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name;