表空間增長率監控腳本(原創)

由于最近業務量大增大，導緻表空間增長速度變得很快，客戶也開始擔憂表空間的增長率。是以也提出了每日監控表空間增長量的需求。筆者根據客戶的需求，在這裡寫了個簡單的腳本，主體思想是通過，将每日查詢到的表空間增長率插入到自己建的表中，然後通過構造查詢語句，反映出表空間的增長率，具體實施不走如下
在資料主機上建立tbs_usage表反映資料中資料檔案的使用量，其中tbs_timeid為該表主鍵，作為唯一辨別當日資料庫表空間的id構造tbs_timeid為df.tablespace_name||'-'||(sysdate)
 1、pansky使用者作為日常管理，目前主要使用者表空間資料量的監控
 SQL> create user pansky identified by pansky default tablespace users quota 50M on users;
 User created. 
 
 SQL> grant create session to pansky;
 Grant succeeded. 
 
 SQL> grant create table to pansky;
 Grant succeeded. 
 
 SQL> grant select on dba_data_files to pansky;
 Grant succeeded. 
 
 SQL>  grant select on dba_free_space to pansky;
 Grant succeeded. 

 2、以pansky使用者建立tbs_usage表
 create table tbs_usage
 as
 SELECT df.tablespace_name||'-'||(sysdate) tbs_timeid ,df.tablespace_name||'-'||(sysdate-1) ys_tbs_timeid,df.tablespace_name,
 COUNT(*) datafile_count,
 ROUND(SUM(df.BYTES) / 1048576) size_mb,
 ROUND(SUM(free.BYTES) / 1048576, 2) free_mb,
 ROUND(SUM(df.BYTES) / 1048576 - SUM(free.BYTES) / 1048576, 2) used_mb,
 ROUND(MAX(free.maxbytes) / 1048576, 2) maxfree,
 100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
 ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free,(sysdate) time
 FROM dba_data_files df,
 (SELECT tablespace_name,
 file_id,
 SUM(BYTES) BYTES,
 MAX(BYTES) maxbytes
 FROM dba_free_space
 GROUP BY tablespace_name, file_id) free
 WHERE df.tablespace_name = free.tablespace_name(+)
 AND df.file_id = free.file_id(+)
 GROUP BY df.tablespace_name
 ORDER BY 8; 

 3、建立主鍵限制
 alter table tbs_usage add constraint tbs_usage_pk_tbs_timeid primary key(tbs_timeid); 

 4、在crontab中運作每日7點30分更新資料庫表空間資訊的腳本update_tbs_info.sh
 30 07 * * * /oracle10g/update_tbs_info.sh  
 其中 update_tbs_info.sh腳本内容如下
 #!/bin/ksh
 #FileName: update_tbs_info.sh
 #CreateDate:2011-10-09
 #Discription:take the basic information to  insert into  the table tbs_usage
 PATH=/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin:/home/                                                                   oracle/bin:/oracle10g/app/oracle/product/10.2.0/db_1/bin;export PATH

 ORACLE_SID=zgscdb1;export ORACLE_SID
 ORACLE_BASE=/oracle10g/app/oracle;export ORACLE_BASE
 ORACLE_HOME=/oracle10g/app/oracle/product/10.2.0/db_1;export ORACLE_HOME
 PATH=$ORACLE_HOME/bin:$PATH;export PATH

 date >> /oracle10g/log/update_tbs_info.log
 sqlplus pansky/pansky <<EOF  >> /oracle10g/log/update_tbs_info.log 2>&1
 insert into pansky.tbs_usage
 SELECT df.tablespace_name||'-'||(sysdate) tb_timeid,df.tablespace_name||'-'||(sysdate-1) y                                                                   s_tb_timeid,df.tablespace_name,
 COUNT(*) datafile_count,
 ROUND(SUM(df.BYTES) / 1048576) size_mb,
 ROUND(SUM(free.BYTES) / 1048576, 2) free_mb,
 ROUND(SUM(df.BYTES) / 1048576 - SUM(free.BYTES) / 1048576, 2) used_mb,
 ROUND(MAX(free.maxbytes) / 1048576, 2) maxfree,
 100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
 ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free,sysdate time
 FROM dba_data_files df,
 (SELECT tablespace_name,
 file_id,
 SUM(BYTES) BYTES,
 MAX(BYTES) maxbytes
 FROM dba_free_space
 GROUP BY tablespace_name, file_id) free
 WHERE df.tablespace_name = free.tablespace_name(+)
 AND df.file_id = free.file_id(+)
 GROUP BY df.tablespace_name
 ORDER BY 8;
 commit;
 EOF
 echo >> /oracle10g/log/update_tbs_info.log 

 4、查詢資料庫表空間使用情況的SQL,下例可查詢出2011-10-08的表空間使用情況以及相較于2011-10-09日的表空間增長量(MB),并根據pct_used降序排列。 
 Set linesize 150
 Col tablespace_name for a22
 select a.tablespace_name,a.datafile_count,a.size_mb,a.free_mb,a.used_mb,a.maxfree,a.pct_used,a.pct_free,to_char(a.time,'yyyy-mm-dd hh24:mi') time,(a.USED_MB-b.USED_MB) increase_mb from pansky.tbs_usage a,pansky.tbs_usage b
 where a.YS_TBs_TIMEid= b.TBs_TIMEid
 and a.time>=to_date('2011-11-02','yyyy-mm-dd') and a.time< to_date('2011-11-03','yyyy-mm-dd')  order by pct_used desc;