Oracle脚本（一）

  公司的库里面不是所有的表空间都是unlimted，所以还是要搞个邮件发送，时不时监控一下表空间的使用率。以下是我参考一些文档，修改后在我工作中使用的。我的数据库版本是11.2.0.4，Linux版本是Centos 6.3。

#!/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  

©著作权归作者所有：来自51CTO博客作者waldens的原创作品，如需转载，请注明出处，否则将追究法律责任

Oracle监控Oracle监控脚本Script