How to check tablespace utilization in Oracle: Easy Guide

This post will cover everything you need to know about tablespace utilization in Oracle. Let’s open virtualBox and get started!

1. Check Free Space in all the tablespaces:

set pages 46000 lines 32760
col tablespace_name format a35
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 x.tablespace_name
        ,x.alloc_size/1024/1024/1024 Allocated_size
        ,x.cur_size/1024/1024/1024 Current_Size
        ,(y.used+x.file_count*65536)/1024/1024/1024 Used_size
        ,(x.alloc_size-(y.used+x.file_count*65536))/1024/1024/1024 Available_size
        ,((y.used+x.file_count*65536)*100)/x.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) y
        ,(select z.tablespace_name
        ,sum(greatest(z.bytes,nvl(z.maxbytes,0))) alloc_size
        ,sum(z.bytes) cur_size
        ,count(*) file_count
        from dba_data_files z
        group by z.tablespace_name) x
where t.tablespace_name=y.tablespace_name
and t.tablespace_name=x.tablespace_name
order by Pct_used desc
/

How to check tablespace utilization in Oracle: Easy Guide

If you want to check any particular tablespace, then you can follow the below query:

2. Check free space in a particular tablespace:

set linesize 200
select x.tablespace_name
               ,sum(x.tot)/1024/1024 "Total Size(GB)"
               ,sum(x.sum)/1024/1024 "Total Free(GB)"
           ,sum(x.sum)*100/sum(x.tot) "% Free"
                    ,sum(x.tot-x.sum)*100/sum(x.tot) "% Used"
          ,sum(x.largest)/1024/1024 "Max Free(GB)"
           from
          (
            select tablespace_name
               ,0 tot
               ,sum(bytes) sum
              ,max(bytes) largest
        from dba_free_space x
      group by tablespace_name
      union
      select tablespace_name
            ,sum(bytes) tot
            ,0
            ,0
       from dba_data_files
      group by tablespace_name) x
        where x.tablespace_name ='&Tablespace_Name'
        group by x.tablespace_name
     ;