Oracle Check undo tablespace usage

Check the undo tablespace total, free and used space(Size in MB) in Oracle

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 like '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                           163835 163827.375      7.625

with 
free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ), 
a as ( select tablespace_name , 
              sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , 
              sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb 
       from dba_undo_extents 
       where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') 
       group by tablespace_name ) , 
undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb 
             from dba_tablespaces ts join dba_data_files df using (tablespace_name) 
             where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) 
select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total 
from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name);

TABLESPACE_NAME                USER_SZ_GB    FREE_GB REUSABLE_SPACE_GB ALLOCATED_GB      TOTAL
------------------------------ ---------- ---------- ----------------- ------------ ----------
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281
UNDOTBS1                       31.9980469 15.3109741        .003051758   176.674255 191.988281

Check the Active, expired and unexpired transaction space usage in Undo Tablespace

ACTIVE: Status show us the active transaction going in database, utilizing the undo tablespace and cannot be truncated.

EXPIRED: Status show us the transaction which is completed and complete the undo_retention time and now first candidate for trucated from undo tablespace.

UNEXPIRED: Status show us the transaction which is completed but not completed the undo retention time. It can be trucated if required.

select tablespace_name tablespace, status, 
       sum(bytes)/1024/1024 sum_in_mb, 
       count(*) counts
from dba_undo_extents
group by tablespace_name, status order by 1,2;

TABLESPACE                     STATUS     SUM_IN_MB     COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1                       ACTIVE        64.125          3
UNDOTBS1                       EXPIRED            3         48
UNDOTBS1                       UNEXPIRED 198002.688       6567

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Add UNDO tablespace datafile

alter tablespace undotbs1 add datafile '/oracle/oradata/orcl/undotbs05.dbf' size 32767M;