Day: September 18, 2024

1. To check the current size of the Undo tablespace:

SQL>  select sum(a.bytes)/1024/1024/1024  as undo_size_GB, c.tablespace_name from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts# group by c.tablespace_name;

UNDO_SIZE_GB TABLESPACE_NAME
------------ ------------------------------
  31.9990234 UNDOTBS1
  31.9990234 UNDOTBS2

2. To check the free space (unallocated) space within Undo tablespace:

SQL> select sum(bytes)/1024/1024/1024 "GB",  tablespace_name from dba_free_space where tablespace_name like 'UNDO%' group by  tablespace_name;

        GB TABLESPACE_NAME
---------- ------------------------------
22.1591797 UNDOTBS1
31.1994629 UNDOTBS2

3.To Check the space available within the allocated Undo tablespace:

SQL>  select tablespace_name, sum(blocks)*8/1024/1024 reusable_space_gb from dba_undo_extents where status='EXPIRED' group by tablespace_name;

TABLESPACE_NAME                REUSABLE_SPACE_GB
------------------------------ -----------------
UNDOTBS1                               9.3729248
UNDOTBS2                              .749023438

4. To Check the space allocated in the Undo tablespace:

SQL>  select tablespace_name, sum(blocks)*8/1024/1024 space_in_use_gb from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;

TABLESPACE_NAME                SPACE_IN_USE_GB
------------------------------ ---------------
UNDOTBS1                            .437988281
UNDOTBS2                            .041503906

 Alternatively, below one SQL can be used as well:

col TABLESPACE_NAME format a10
col USER_SZ_GB format 99.999
col FREE_GB format 999.99
col USER_SZ_GB format 999.99
col REUSABLE_SPACE_GB format 999.99
col ALLOCATED_GB format 999.99
col TOTAL format 999.99

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 USER_SZ_GB FREE_GB REUSABLE_SPACE_GB ALLOCATED_GB   TOTAL
---------- ---------- ------- ----------------- ------------ -------
UNDOTBS1        32.00   22.16              9.39          .44   32.00
UNDOTBS2        32.00   31.20               .76          .04   32.00