Oracle监视表空间,并自动增加数据文件脚本(二)
###################################################
## checkTabsp.sh ##
## This Script will add the new datafile if BOCC Tablespace's data
## file, which is greater than the 80% of one datafiles size
#####################################################################
# Avoid have the 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 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" <
ALTER TABLESPACE DB_TABLESPACE ADD DATAFILE '/opt/oracle/oradata/DB/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
col 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 don't 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
# fi