Table space Space availability

Below is the query to identify the available table space's space in MB, if in case you tend to use the Tablespace in creating database objects.

SELECT b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb, c.totalusedspace UsedMB
  FROM (SELECT tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, sum(bytes)/1024/1024 as tbs_size
          FROM dba_data_files
         GROUP BY tablespace_name
         UNION
        SELECT tablespace_name, sum(bytes)/1024/1024 tbs_size
          FROM dba_temp_files
         GROUP BY tablespace_name) b,
        (SELECT tablespace_name, round(sum(bytes)/1024/1024, 2) as totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) c
 WHERE a.tablespace_name(+) = b.tablespace_name
   AND a.tablespace_name    = c.tablespace_name;

Print This Post