Space Usage on Table spaces

set lines 132
set pages 100
clear break 
clear compute
ttitle center 'Space Usage on Tablespaces' skip 1 
center '*****************************************' skip 2
accept tablespace_name char prompt 'Enter Tablespace Name :'
spool check_free_space
select a.TABLESPACE_NAME,
       round(a.bytes_used/(1024*1024),2) TOTAL_SPACE_IN_MB,
       round(b.bytes_free/(1024*1024),2) FREE_SPACE_IN_MB,
--       round(b.smallest/(1024*1024),2) min_size_in_MB,
--       round(b.largest/(1024*1024),2) max_size_in_MB,
       round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used
  from
  ( select TABLESPACE_NAME, sum(bytes) bytes_used
    from dba_data_files
   group by TABLESPACE_NAME
  ) a,
  ( select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest
    from dba_free_space
   group by TABLESPACE_NAME
  ) b
 where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
   and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name')
 order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc;
spool off

-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, 
dd.bytes/1024 TABLESPACE_KB, 
SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

-- list datafiles, tablespace names, and size in MB:
col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;