db_space.sql

This SQL is not the perfect one, but good enough.

set pagesize 80
col used_prcnt format 999.99
col allocated_mb format 999,999
col used_mb format 999,999
col free_mb format 999,999


select ddf.tablespace_name,
       sum(ddf.allocated_mb) as allocated_mb,
       sum(ddf.allocated_mb-dfs.bytes) as used_mb,
       sum(dfs.bytes) as free_mb,
       round(100*(sum(ddf.allocated_mb-dfs.bytes)/sum(ddf.allocated_mb)),2) as Used_prcnt
  from
       (select substr(file_name,1,instr(file_name,'/',1,3)-1) as LUN,
               TABLESPACE_NAME,
               round(sum(bytes)/1024/1024,2) Allocated_MB
          from dba_data_files
         group by substr(file_name,1,instr(file_name,'/',1,3)-1),
                  TABLESPACE_NAME)  ddf,
       (select TABLESPACE_NAME,  round(sum(bytes)/1024/1024,2) as bytes
          from dba_free_space
         group by TABLESPACE_NAME
       ) dfs
 where
    ddf.tablespace_name=dfs.tablespace_name(+)
  group by
  ddf.tablespace_name
order by Used_prcnt desc
/