select q1.tablespace_name, q1.file_id
      ,trunc(q1.all_space/1024/1024) all_space
      ,trunc(q2.free_space/1024/1024) free_space
  from (select tablespace_name, file_id, sum(user_bytes) all_space
          from dba_data_files
         group by tablespace_name, file_id) q1
      ,(select tablespace_name, file_id, sum(bytes) free_space
          from dba_free_space free
         group by tablespace_name, file_id) q2
 where q1.tablespace_name = q2.tablespace_name(+)
   and q1.file_id = q2.file_id(+)
  order by q1.tablespace_name, q1.file_id;

Comments powered by CComment