Oracle监视表空间并自动增加数据文件脚本.docx

1、Oracle监视表空间并自动增加数据文件脚本Oracle监视表空间,并自动增加数据文件脚本 Oracle监视表空间,并自动增加数据文件脚本 Sql代码 - 创建view - 百分比 create view tablespace_used_percent as select useage from ( select a.tablespace_name, a.file_name, a.total Total(MB), round(a.total-b.Free_Space) Used(MB), round(a.total-b.Free_Space)/a.total)*100,2) as useage

2、, a.auto_extend from (select FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENSIBLE auto_extend from dba_data_files ddf) a, (select file_id, sum(bytes)/(1024*1024) Free_Space from dba_free_space group by file_id) b where a.file_id=b.file_id ) where tablespace_name = YOUR TABLES

3、PACE NAME; - deails create view TABLESPACE_USAGE as select a.tablespace_name, a.file_name, a.total Total(MB), round(a.total-b.Free_Space) Used(MB), round(a.total-b.Free_Space)/a.total)*100,2) Used(%), a.auto_extend from (select FILE_ID, tablespace_name, file_name, bytes/(1024*1024) Total, AUTOEXTENS

4、IBLE auto_extend from dba_data_files ddf) a, (select file_id, sum(bytes)/(1024*1024) Free_Space from dba_free_space group by file_id) b where a.file_id=b.file_id; Linux 脚本 Java代码 # # checkTabsp.sh # # This Script will add the new datafile if Tablespaces data # file, which is greater than the 80% of

5、one datafiles size # #!/bin/bash usedPercentNO=(sqlplus -s /as sysdba <<EOF SET heading OFF; SET verify OFF; SELECT * FROM tablespace_used_percent; EOF ) #get the length of array len=$#usedPercentNO* echo The array has $len members. i=0 while $i -lt $len ; do echo $i: $usedPercentNO$i arrNo=ec

6、ho $usedPercentNO$i | awk -F. print $1 if -z $arrNo then arrNo=1 fi # if usedPercentNo >= 80 then we add new data file,which will have 8G size if $arrNo -gt 80 then let sigNo=$i+1 sqlplus -s / as sysdba <<EOF ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE /opt/oracle/oradata/DB/DB_DATA$sigNo.d

7、bf SIZE 2G AUTOEXTEND ON MAXSIZE 8G; EOF # we need send email to report the tablespace stats info sqlplus -s /as sysdba <<EOF col tablespace_name for a30 col file_name for a60 col auto_extend for a12 col tablespace_name justify center col file_name justify center col autoextend justify right s

8、et linesize 200 set pagesize 500 SPOOL tablespace.alert SELECT * FROM TABLESPACE_USAGE; SPOOL OFF; EXIT EOF fi let i+ done #we neednt send email from there the crontab will do if cat tablespace.alert|wc -l -gt 0 then cat tablespace.alert >tablespace.tmp mailx -s TABLESPACE ALERT for DB EMAIL-ADDR

9、ESS < tablespace.alert fi 上面这个脚本会导致如果有一个文件超过80%的话,脚本会不停添加数据文件. 更新修改后的,而且把sql直接用文本文件来代替了以前使用的view Java代码 #!/bin/bash # Managed by Puppet # # checkTabsp.sh # # This Script will add the new datafile if BOCC Tablespaces data # file, which is greater than the 80% of one datafiles size # # Avoid have t

10、he script run if already running source /opt/app/inc/some_functions.sh pgrpfile=/tmp/checkTabsp.pgrp check_if_running # end source /home/oracle/.profile usedDatafileNO=(sqlplus -s /as sysdba <<EOF SET heading OFF; SET verify OFF; /opt/app/sql/chktabspused.sql EOF ) # check whether it needs add

11、 data file if $usedDatafileNO -eq 0 then usedDatNO=(sqlplus -s /as sysdba <<EOF SET heading OFF; SET verify OFF; /opt/app/sql/chkdatno.sql EOF ) let sigNO=$usedDatNO+1 sigNO=printf %03d $sigNO sqlplus -s / as sysdba <<EOF ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE /opt/oracle/oradata/DB

12、/DB_DATA$sigNO.dbf SIZE 500M AUTOEXTEND ON NEXT 50M; EOF # we need send email to report the tablespace stats info to check whether add data file successful sqlplus -s /as sysdba <<EOF col tablespace_name for a30 col file_name for a60 col auto_extend for a12 col tablespace_name justify center c

13、ol file_name justify center col autoextend justify right set linesize 200 set pagesize 500 /opt/bocc/sql/chktabspstats.sql EXIT EOF # out put the disk space useage df -h fi #we will dont send email from there the crontab will do # if cat tablespace.alert|wc -l -gt 0 # then # cat tablespace.alert >tablespace.tmp # mailx -s TABLESPACE ALERT for DB YOUR_EMAIL_ADDRESS t < tablespace.alert