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

转自：http://lovejuan1314.javaeye.com/blog/413546

 

--- 创建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,
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 TABLESPACE 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,
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;

Linux 脚本

#####################################################################
## checkTabsp.sh ##
## This Script will add the new datafile if  Tablespace's data
## file, which is greater than  the 80% of 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=`echo "${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.dbf' 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
        set linesize 200
        set pagesize 500
        SPOOL tablespace.alert 
        SELECT * FROM TABLESPACE_USAGE;
        SPOOL OFF;
        EXIT
EOF
      fi
  let i++
done

#we needn'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"  EMAIL-ADDRESS  < tablespace.alert
  fi

上面这个脚本会导致如果有一个文件超过80%的话,脚本会不停添加数据文件....

更新修改后的,而且把sql直接用文本文件来代替了以前使用的view

#!/bin/bash

# Managed by Puppet

#####################################################################
## 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" <<EOF
       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