Tablespace management in an Oracle database is very much needed and something a DBA needs to do act on it very frequently. It’s DBA job to maintain enough space in the tablespaces in order to have smooth functioning of application and database. This tablespace query can be very helpful when you are trying to understand what tablespaces you have in your database, and what data files are associated with those tablespaces. In this query, we use DBA_TABLESPACES to extract each tablespace name. We have also joined in DBA_SEGMENTS to determine how much free space we have left in our tablespace. This gives us some idea about future growth and tablespace utilization at a glance.
Index
Query To Check Tablespace Utilization
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading “Tablespace|Name”
col Allocated_size heading “Allocated|Size(GB)” form 99999999.99
col Current_size heading “Current|Size(GB)” form 99999999.99
col Used_size heading “Used|Size(GB)” form 99999999.99
col Available_size heading “Available|Size(GB)” form 99999999.99
col Pct_used heading “%Used (vs)|(Allocated)” form 99999999.99
Â
select a.tablespace_name
    ,a.alloc_size/1024/1024/1024 Allocated_size
    ,a.cur_size/1024/1024/1024 Current_Size
    ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
    ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
    ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from   dba_tablespaces t
    ,(select t1.tablespace_name
    ,nvl(sum(s.bytes),0) used
    from dba_segments s
    ,dba_tablespaces t1
     where t1.tablespace_name=s.tablespace_name(+)
     group by t1.tablespace_name) u
    ,(select d.tablespace_name
    ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
    ,sum(d.bytes) cur_size
    ,count(*) file_count
    from dba_data_files d
    group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name and t.tablespace_name=a.tablespace_name order by t.tablespace_name;
Query Output
