Find tablespace size, used space and free space in Oracle database

• ·        Tablespace Name
• ·        % used
• ·        Space allocated to tablespace
• ·        Space used in tablespace
• ·        Space free in tablespace
• ·        Number of datafiles used by tablespace

SELECT  a.tablespace_name,

    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,

    c.BYTES / 1024 / 1024 space_allocated,

    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,

    ROUND (NVL (b.BYTES, 0) / 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;