set pagesize 100 lines 132 echo off verify off feedb off space 1 trimsp on
compute sum of a_byt t_byt f_byt on report
break on report on tablespace_name on pf
col tablespace_name for a17 tru head ‘Tablespace|Name’
col file_name for a40 tru head ‘Filename’
col a_byt for 9,990.999 head ‘Allocated|GB’
col t_byt for 9,990,999 head ‘Current|Used GB’
col f_byt for 9,990,999 head ‘Current|Free GB’
col pct_free for 990.0 head ‘File %|Free’
col pf for 990.0 head ‘Tbsp %|Free’
col seq noprint
define b_div=1073741824

select 1 seq,b.tablespace_name,nvl(x.fs,0)/y.ap*100 pf,b.file_name file_name,b.bytes/&&b_div a_byt,nvl((b.bytes-sum(f.bytes))/&&b_div,b.bytes/&&b_div) t_byt,nvl(sum(f.bytes)/&&b_div,0) f_byt,nvl(sum(f.bytes)/b.bytes*100,0) free
from dba_free_space f,dba_data_files b,
(select y.tablespace_name,sum(y.bytes) fs from dba_free_space y group by y.tablespace_name) x,
(select x.tablespace_name,sum(x.bytes) ap from dba_data_files x group by x.tablespace_name) y
and x.tablespace_name(+)=y.tablespace_name
and y.tablespace_name=b.tablespace_name
and f.tablespace_name(+)=b.tablespace_name
group by
select 2 seq,tablespace_name,*100 pf, file_name,b.bytes/&&b_div a_byt,a.bytes_used/&&b_div t_byt,a.bytes_free/&&b_div f_byt,a.bytes_free/&&b_div pct_free
from v$temp_space_header a,v$tempfile b,
(select sum(bytes_free) bf from v$temp_space_header) j,
(select sum(bytes) bb from v$tempfile) k
where a.file_id = b.file#
order by 1,2,4,3;


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.