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,
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 脚本
Java代码
#####################################################################
## 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
Java代码
#!/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
<
