Oracle监控脚本

zd6

公司的库里面不是所有的表空间都是unlimted，所以还是要搞个邮件发送，时不时监控一下表空间的使用率。用的数据库版本是11.2，Linux版本是Centos 6.3.

[Shell] 纯文本查看 复制代码

#!/bin/bash 
ORACLE_SID=orcl; export ORACLE_SID 
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE 
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME 
$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; 
export PATH 
DBALIST="masicong1019@hotmail.com";export DBALIST 
 
sqlplus -s '/ as sysdba' <<EOF 
set feed off 
set linesize 100 
set pagesize 200 
column "USED (MB)" format a10 
column "FREE (MB)" format a10 
column "TOTAL (MB)" format a10 
column PER_FREE format a10 
spool tablespace.alert 
SELECT F.TABLESPACE_NAME, 
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)", 
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", 
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)", 
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE 
FROM ( 
SELECT TABLESPACE_NAME, 
ROUND (SUM (BLOCKS*(SELECT VALUE/1024 
FROM V$PARAMETER 
WHERE NAME = 'db_block_size')/1024) 
) FREE_SPACE 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME 
) F, 
( 
SELECT TABLESPACE_NAME, 
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE 
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME 
) T 
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME 
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80; 
spool off 
exit 
EOF 
if [cat tablespace.alert|wc -l -gt 0] 
then 
cat tablespace.alert > tablespace.tmp 
mailx -s "TABLESPACE  ALERT  for  PPRD10" $DBALIST < tablespace.tmp 
fi 

liuming794

长大了娶唐僧做老公，能玩就玩一玩，不能玩就把他吃掉。

心海恋歌

学习了，谢谢分享、、、

gyts62

沙发！沙发!

座机

爱——不是想出来的，爱——是做出来的!!

wuaji

不错不错，楼主您辛苦了。。。

qqruser

内练一口气，外练一口屁。