怎麼監控oracle數據庫表和表空間的現狀
declare
unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;
begin
dbms_space.space_usage('&1',
'&2',
'TABLE',
unf,
unfb,
fs1,
fs1b,
fs2,
fs2b,
fs3,
fs3b,
fs4,
fs4b,
full,
fullb);
dbms_output.put_line('unformatted_blocks:' || unf);
dbms_output.put_line('full_blocks(0% free):' || full);
dbms_output.put_line('fs1_blocks(0-25% free):' || fs1);
dbms_output.put_line('fs2_blocks(25-50% free):' || fs2);
dbms_output.put_line('fs3_blocks(50-75% free):' || fs3);
dbms_output.put_line('fs4_blocks(75-100% free):' || fs4);
end;
/
2. 表空間的使用歷史記錄(只輸出了每天的第一次統計結果)
select b.name,
a.rtime,
a.tablespace_usedsize,
a.tablespace_size,
round(100 * a.tablespace_usedsize / a.tablespace_size) used_percent
from dba_hist_tbspc_space_usage a,
(select t2.name,
min(rtime) rtime,
min(tablespace_id) tablespace_id
from dba_hist_tbspc_space_usage t1
inner join v$tablespace t2 on t1.tablespace_id = t2.TS#
where t2.NAME = upper('&1')
group by name, substr(rtime,1,10)
) b
where a.tablespace_id = b.tablespace_id
and a.rtime = b.rtime
order by a.rtime;
3. 表的剩余空間預測
select * from table(dbms_space.OBJECT_GROWTH_TREND('ECC_CC', 'YTKC_ORDERRELEVANCE', 'TABLE')
