Query to find Tablespace utilization in Oracle Database

Oracle Tablespace Utilization

Find the tablespace utilization on Oracle Database (10g,11g,12c,19c)

column file_name format a45

column tablespace_name format a10

col tablespace_name for a40

set verify off

set pages 3000

set line 3000

 

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes* 100, 0), '990.00') free_pct,

decode(sign((NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes/1024/1024, 0)),-1,0,

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB" FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

order by free_pct;

Best Wishes!!