From Oramosswiki
--******************************************************************************
--*
--* Description
--* ***********
--*
--* File: get_tablespace_space_usage_metrics.sql
--*
--* Description: This script shows the following metrics by tablespace:
--* Total tablespace size (GB)
--* Used segment size (GB)
--* Free size (GB) calculated as Total size - Used segment size
--* Free size based on DBA_FREE_SPACE.
--*
--* ----------------------------------------------------------------------------
--* Date Author Description
--* =========== ================= ============================================
--* 27-JUL-2009 Jeff Moss Created
--*
WITH ddf AS
(
SELECT tablespace_name
, ROUND(SUM(bytes)/(1024*1024*1024)) size_gb
FROM dba_data_files
GROUP BY tablespace_name
)
, ds AS
(
SELECT tablespace_name
, ROUND(SUM(bytes/(1024*1024*1024))) used_size_gb
FROM dba_segments
GROUP BY tablespace_name
)
, dfs AS
(
SELECT tablespace_name
, ROUND(SUM(bytes/(1024*1024*1024))) free_size_gb
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT ddf.tablespace_name
, ddf.size_gb total_size_gb
, NVL(ds.used_size_gb,0) used_size_gb
, (ddf.size_gb - NVL(ds.used_size_gb,0)) free_size_gb
, NVL(dfs.free_size_gb,0) free_size_gb_dfs
FROM ddf
, ds
, dfs
WHERE ddf.tablespace_name = ds.tablespace_name(+)
AND ddf.tablespace_name = dfs.tablespace_name(+)
ORDER BY ddf.tablespace_name
/
