SQL Script List all Tablespace FreeSpace

Updated: 30-Mar-2002
Version: 2.0

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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

CLEAR
SET HEAD ON
SET VERIFY OFF
SET PAGES 200

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

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links