SQL Script List All Tablespace freespace

Updated : 30-March-2000
Version : 1.3

Description

This script tablespace free space in bytes and percentage. Totals at foot of report.

Parameters

&tablespace_name - RETURN for all, enter pattern or single tablespace name.

SQL Source

CLEAR
SET HEAD ON
SET VERIFY OFF

PROMPT 
ACCEPT tablespace_name PROMPT 'Enter Value Tablespace (Return For all) : ' 

col tspace form a20 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"

break on report
compute sum of tot_ts_size on report
compute sum of free_ts_size on report

select df.tablespace_name tspace, 
       df.bytes/(1024*1024) tot_ts_size,
       sum(fs.bytes)/(1024*1024) free_ts_size,
       round(sum(fs.bytes)*100/df.bytes) ts_pct,
       round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1,
       ROUND(100*SQRT(MAX(fs.bytes)/SUM(fs.bytes))*
  		(1/SQRT(SQRT(COUNT(fs.bytes)))) ,2) FSFI
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes
				 from dba_data_files
 				 group by tablespace_name ) df          
where fs.tablespace_name = df.tablespace_name
and df.tablespace_name like UPPER('%&tablespace_name%')
group by df.tablespace_name, df.bytes
/

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal