set echo off

set feedback off

set heading off

set termout off

set verify off

set linesize 80

set newpage 1

set pagesize 999



CREATE VIEW dictools_free_space AS

SELECT   t.name tablespace_name,

         SUM(fet.length)*t.blocksize/(1024*1024) free_space,

         COUNT(fet.length) no_free_ext,

         MAX(fet.length)*t.blocksize/(1024*1024) max_free_ext

FROM     sys.ts$ t,

         sys.fet$ fet

WHERE    fet.ts# = t.ts#

GROUP BY t.name,

         t.blocksize

/



set termout on



SELECT   'Space stats Instance ' || name || '.  Time: ' ||

         TO_CHAR(sysdate, 'DD-Mon-YYYY HH24:MI:SS')

FROM     v$database

/



set heading on

break on report

column tablespace_name format a15 heading 'Tablespace Name'

column sz format 999,990.90 heading 'Size (MB)'

column free_space format 999,990.90 heading 'Free (MB)'

column fs format 999,990.90 heading 'Free (MB)'

column nfe format 99,999 heading 'No of|Free|Exts'

column mfe format 99990.90 heading 'Biggest|Free|Ext (MB)'

column pctused Format 999.99 heading '% Used'

compute sum of sz fs on report

--ttitle center 'Free Space in the Database'-

--right 'Page:' format 999 sql.pno skip skip

--prompt

--prompt Print Free database space



SELECT   ts.tablespace_name,

         SUM(df.bytes)/(1024*1024) sz,

         fs.free_space fs,

         (((SUM(df.bytes)/(1024*1024)) - fs.free_space )*100 )/

         (SUM(df.bytes)/(1024*1024)) pctused,

         fs.no_free_ext nfe,

         fs.max_free_ext mfe

FROM     sys.dba_data_files df,

         sys.dba_tablespaces ts,

         dictools_free_space fs

WHERE    df.tablespace_name = ts.tablespace_name

AND      fs.tablespace_name = ts.tablespace_name

GROUP BY ts.tablespace_name,

         fs.free_space,

         fs.no_free_ext,

         fs.max_free_ext

--ORDER BY ts.tablespace_name

/



DROP VIEW dictools_free_space

/



--prompt End of Report