UNDO Tablespace total and used size in MB or GB

To check the size of each undo tablespace along with total and used sizes.

--Undo TBS size in MB

select

a. tablespace_name,

sum(a.bytes)/(1024*10240) "Allocation in MB",

sum(b.bytes)/(1024*10240) "Used in MB"

from

dba_data_files a,

dba_segments b

where

a.tablespace_name = b.tablespace_name

and

a.tablespace_name in (select tablespace_name from dba_undo_extents) 

group by

a.tablespace_name;

--Undo TBS size in GB

set linesize 200

set pagesize 200  

select 

   tablespace_name, 

   --round(TABLESPACE_SIZE/1024,2) size_MB,

   round(TABLESPACE_SIZE/1024/1024,2) size_GB,

   round(used_space/1024/1024,2) Used_MB,

   --round(used_space/1024/1024,2) Used_GB,

   round(used_percent,2) Percentage_used

from

   dba_tablespace_usage_metrics

 where tablespace_name in (select tablespace_name from dba_undo_extents) 

order by 2;