Tablespace % free check

set feed off
set linesize 200


column "tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
column "%Free" heading "% | Free" format 999.99
column "%Free Ext" heading "% | Free Ext" format 999.99
column "Graph" heading "Graph" format a11
column tablespace_name heading "Tablespace | Name" format a20




SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc
/


prompt
prompt "The last line shows the Tablespace wich is growing too large..."
prompt


***************************************************************

rem
rem Name:     free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: this report uses the free_space2 view
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace      HEADING Name             FORMAT a30
COLUMN files           HEADING '#Files'         FORMAT 9,999
COLUMN pieces          HEADING 'Frag'           FORMAT 9,999
COLUMN free_bytes      HEADING 'Free|Byte'      FORMAT 9,999,999,999
COLUMN free_blocks     HEADING 'Free|Blk'       FORMAT 999,999
COLUMN largest_bytes   HEADING 'Biggest|Bytes'  FORMAT 9,999,999,999
COLUMN largest_blks    HEADING 'Biggest|Blks'   FORMAT 999,999
COLUMN ratio           HEADING 'Percent'        FORMAT 999.999
COLUMN average_fsfi    HEADING 'Average|FSFI'   FORMAT 999.999
START title132 "FREE SPACE REPORT"
DEFINE 1 = report_output/&&db/free_spc
SPOOL &1
SELECT
       tablespace,
       COUNT(*) files,
       SUM(pieces) pieces,
       SUM(free_bytes) free_bytes,
       SUM(free_blocks) free_blocks,
       SUM(largest_bytes) largest_bytes,
       SUM(largest_blks) largest_blks,
       SUM(largest_bytes)/sum(free_bytes)*100 ratio,
       SUM(fsfi)/COUNT(*) average_fsfi
FROM
       free_space
GROUP BY
       tablespace;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEED ON
SET FLUSH ON
SET VERIFY ON
SET PAGES 22 LINES 80
PAUSE Press Enter to continue


**************************************************************



spool tbs_freespace.txt
    undefine Tablespace
    set pages 200
    set lines 120
    select a.tablespace_name,
    SUM((BYTES/1024)/1024) "Used Space(MB)",
    SUM(total)  "allocated size(MB)",
    SUM(max) "maximum allowable (MB)",
    SUM(max-(BYTES/1024)/1024) "effective free(MB)",
    SUM(round(((max-(BYTES/1024)/1024)/max)*100,2)) "% FREE"
      from SM$TS_USED a,
      (
       select tablespace_name,sum((BYTES/1024)/1024) total,
       sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
       from dba_data_files
       group by tablespace_name
      ) b where
      a.tablespace_name = b.tablespace_name
      group by
       a.tablespace_name
    order by 6 asc;
    spool off


************************************************************

COLUMN dummy noprint
COLUMN  pct_used format 999.9       heading "Used%"
COLUMN  name    format a25      heading "Tablespace Name"
COLUMN  Kbytes   format 999,999,999    heading "MBytes"
COLUMN  used    format 999,999,999   heading "MB Used"
COLUMN  free    format 999,999,999  heading "MB Free"
COLUMN  largest    format 999,999,999  heading "MB Largest"
BREAK   on report
COMPUTE sum LABEL "Total (in MB)" of kbytes on report
COMPUTE sum of free on report
COMPUTE sum of used on report


SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) name,
kbytes_alloc / 1024 kbytes,
(  kbytes_alloc - NVL (kbytes_free, 0)) / 1024 used,
NVL (kbytes_free, 0) / 1024 free,
(  ((  kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc)* 100) pct_used,
NVL (largest, 0) / 1024 largest,chunks "Total Chunks"
FROM     (SELECT   SUM (bytes) / 1024 kbytes_free,
MAX (bytes) / 1024 largest,
count(file_id) chunks,
tablespace_name
FROM     sys.dba_free_space
GROUP BY tablespace_name) a,
(SELECT   SUM (bytes) / 1024 kbytes_alloc,
tablespace_name
FROM     sys.dba_data_files
GROUP BY tablespace_name) b
WHERE    a.tablespace_name(+) = b.tablespace_name
union all
SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) name,
kbytes_alloc / 1024 kbytes,
(  kbytes_alloc - NVL (kbytes_free, 0)) / 1024 used,
NVL (kbytes_free, 0) / 1024 free,
(  ((  kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc)* 100) pct_used,
NVL (largest, 0) / 1024 largest,chunks "Total Chunks"
FROM     (SELECT   SUM (bytes) / 1024 kbytes_free,
MAX (bytes) / 1024 largest,
count(file_id) chunks,
tablespace_name
FROM     sys.dba_free_space
GROUP BY tablespace_name) a,
(SELECT   SUM (bytes) / 1024 kbytes_alloc,
tablespace_name
FROM     sys.dba_temp_files
GROUP BY tablespace_name) b
WHERE    a.tablespace_name(+) = b.tablespace_name;
ORDER BY &orderby DESC;