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;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> set pagesize 100 lines 132 echo off verify off feedb off space 1 trimsp on SQL> compute sum of a_byt t_byt f_byt on report SQL> break on report on tablespace_name on pf SQL> col tablespace_name for a17 tru head 'Tablespace|Name' SQL> col file_name for a40 tru head 'Filename' SQL> col a_byt for 9,990.999 head 'Allocated|GB' SQL> col t_byt for 9,990,999 head 'Current|Used GB' SQL> col f_byt for 9,990,999 head 'Current|Free GB' SQL> col pct_free for 990.0 head 'File %|Free' SQL> col pf for 990.0 head 'Tbsp %|Free' SQL> col seq noprint SQL> define b_div=1073741824 SQL> SQL> 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 2 from dba_free_space f,dba_data_files b, 3 (select y.tablespace_name,sum(y.bytes) fs from dba_free_space y group by y.tablespace_name) x, 4 (select x.tablespace_name,sum(x.bytes) ap from dba_data_files x group by x.tablespace_name) y 5 where 6 f.file_id(+)=b.file_id 7 and x.tablespace_name(+)=y.tablespace_name 8 and y.tablespace_name=b.tablespace_name 9 and f.tablespace_name(+)=b.tablespace_name 10 group by 11 b.tablespace_name,nvl(x.fs,0)/y.ap*100,b.file_name,b.bytes 12 union 13 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 14 from v$temp_space_header a,v$tempfile b, 15 (select sum(bytes_free) bf from v$temp_space_header) j, 16 (select sum(bytes) bb from v$tempfile) k 17 where a.file_id = b.file# 18 order by 1,2,4,3; Tablespace Tbsp % Allocated Current Current Name Free Filename GB Used GB Free GB FREE ----------------- ------ ---------------------------------------- ---------- ---------- ---------- ---------- ADAMHUAN 99.0 /ora_data/ALLAH/datafile/o1_mf_adamhuan_ 0.098 0,000 0,000 99 ALLAH 99.0 /ora_data/ALLAH/datafile/o1_mf_allah_b5j 0.098 0,000 0,000 99 SYSAUX 5.2 /ora_data/allah/sysaux01.dbf 0.693 0,001 0,000 5.19366197 SYSTEM 0.7 /ora_data/allah/system01.dbf 0.781 0,001 0,000 .6796875 UNDOTBS1 77.7 /ora_data/allah/undotbs01.dbf 0.132 0,000 0,000 77.7314815 USERS 66.3 /ora_data/allah/users01.dbf 0.005 0,000 0,000 66.25 TEMP 0.0 /ora_data/allah/temp01.dbf 0.192 0,000 0,000 0 ***************** ****** ---------- ---------- ---------- 1.999 0,002 0,000 SQL> |
——————————————————————————————————————
Ending。