计算每日增长量,预测数据库的增长

实现过程:

1.创建用于存放表空间使用情况的表:check_tbs

create table oak.check_tbs(
host varchar2(16),
tablespace_name varchar2(50),
megs_alloc number(8,2),
megs_free number(8,2),
megs_used number(8,2),
Pct_Free number(5,2),
pct_used number(5,2),
max number(8,2),
recorddate date) tablespace tbs_oak;
2.
sys的帐号下添加一job用于没天收集表空间使用情况

insert into oak.check_tbs
select a.host, a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) Pct_used,
round(maxbytes / 1048576, 2) Max,
a.recorddate
from (select '10.182.15.55' as host , f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes, sysdate as recorddate
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from sys.dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)

union all
select '10.182.15.55' as host, h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
1048576,
2) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576, 2) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100,
2) Pct_Free,
100 -
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100,
2) pct_used,
round(f.maxbytes / 1048576, 2) max,
sysdate as recorddate
sys.v_\$Temp_extent_pool  p,
dba_temp_files           f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name, f.maxbytes;

3.创建一临时表用于某一表空间每天的使用量

create global temporary table tbs_temp

(used number(8),

recorddate date);

4.向临时表中插入数据,为统计每天的增长量做准备

insert into tbs_temp

select t.megs_used, trunc(t.recorddate)

from checktablespace t

where t.ip='10.182.15.30'

and t.tablespace_name='TBS_SFCDATA'

and t.recorddate > trunc(sysdate -30);

5.计算每天的增长量

select a.used-b.used, a.recorddate

from tbs_temp a, tbs_temp b

where a.recorddate=b.recorddate+1;

• 博文量
143
• 访问量
269640