作者: ritchy
2011-03-18 15:39:12
[i=s] 本帖最后由 ritchy 于 2011-3-18 16:11 编辑 [/i]
下面脚本只能够运行单实例，想改进下，怎么才能够运行在一个装有oracle的客户端上，监控所有可以访问的数据库。。。。。。
希望各位高手指点或改进
###################################
## chktbs_dbmail.sh ##
## usage:Monitor TableSpace Send Mail ##
## creator: Ritchy ##
## ORACLE_SID -- ##
## v_used_max -- Max Threshold ##
## v_used_min -- Incremental ##
## Mail -- addressee ##
###################################
#!/bin/sh
# set ORACLE_SID
# 实例名
export ORACLE_SID= xxx
sqlplus -s / as sysdba >chktbspace.alert<
#最大警戒值
DEFINE v_used_max = 80.01 ;
#增量
DEFINE v_used_min = 0.05 ;
set serveroutput off
set newp none
set echo off
set feedback off
set heading off
set pagesize 0
set termout off
set trimout on
set trimspool on
set verify off
set autotrace off
set LINESIZE 25000
set feed off
select 'ALTER DATABASE DATAFILE ' || chr(39) || FILE_NAME || chr(39) ||
' RESIZE ' ||ceil(bytes / 1024 / 1024 * (1+&v_used_min))|| 'M;'
from dba_data_files
where tablespace_name in
(select d.tablespace_name
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) >
&v_used_max
AND d.tablespace_name not in
('UNDOTBS1', 'SYSAUX', 'SYSTEM', 'USERS', 'TEMP'));
EXIT
EOF
# set Mail, we need send email to report the tablespace alter info
sqlplus / as sysdba @chktbspace.alert
exit
# 邮件
if [ `cat chktbspace.alert|wc -l` -gt 0 ]
then
cat chktbspace.alert | mail xxx@xxx.com -s "TABLESPACE ALERT !"
fi