This post will cover everything you need to know about tablespace utilization in Oracle. Let’s open virtualBox and get started!
Table of Contents
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
/
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
;
I hope you found this article helpful to check the tablespace utilization in Oracle.
Looking for more? Dive into our comprehensive guide on how to add datafile in tablespace in Oracle.