Oracle SQL：表空间的利用率

Command：
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
where
f.file_id(+)=b.file_id
and x.tablespace_name(+)=y.tablespace_name
and y.tablespace_name=b.tablespace_name
and f.tablespace_name(+)=b.tablespace_name
group by
b.tablespace_name,nvl(x.fs,0)/y.ap*100,b.file_name,b.bytes
union
select 2 seq,tablespace_name,j.bf/k.bb*100 pf,b.name 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;

——————————————————————————————————————
Ending。

感谢支持

赏