Find TABLESPACE Usage size and free space size in Oracle

   
FIND TABLESPACE SIZE USAGE AND FREE SPACE :
-------------------------------------------

select
   tablespace_name,
   used_percent
from
   dba_tablespace_usage_metrics
where
   used_percent > 90;

  SELECT  a.tablespace_name,
    ROUND (((C.BYTES - NVL (B.BYTES, 0)) / C.BYTES) * 100,2) PERCENTAGE_USED,
    C.BYTES / 1024 / 1024/ 1024 SPACE_ALLOCATED,
    ROUND (C.BYTES / 1024 / 1024/ 1024 - NVL (B.BYTES, 0) / 1024 / 1024/ 1024,2) SPACE_USED,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024/ 1024, 2) space_free,
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name,
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES,
                  SUM (BYTES) BYTES,
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name
    and C.TABLESPACE_NAME(+) = a.TABLESPACE_NAME
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;



select a.TABLESPACE_NAME,
  ROUND(a.TOTSIZE    /1024/1024/1024,0) "Total Size (GB)",
  ROUND(NVL(b.used,0)/1024/1024/1024,0) "Used (GB)",
  100                - ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "%Used",
  ROUND(((a.TOTSIZE  - NVL(B.USED,0)) / a.TOTSIZE) * 100,0) "% Free",
  (ROUND(a.totsize   /1024/1024/1024,0)-ROUND(NVL(b.used,0)/1024/1024/1024,0) ) "Free GB"
FROM
  (SELECT tablespace_name,
    SUM(bytes) totsize
  FROM dba_data_files
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,
    SUM(bytes) used
  FROM dba_segments
  GROUP BY tablespace_name
  ) b
where a.TABLESPACE_NAME=B.TABLESPACE_NAME
ORDER BY 1;