auto_send_tablespace定期发送表空间巡检到邮箱

简述：周期定时发送表空间到指定邮箱内

1.修改邮箱配置 /etc/mail.rc，具体细节见网上教程

$ vi /etc/mail.rc
set from=123456@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=123456
set smtp-auth-password=runoob
set smtp-auth=login

测试：
echo hello word | mail -s " title" 123456@qq.com

2.添加脚本到oracle用户的定时任务，可以调整执行时间，这是一周执行一次

[oracle@app-db ~]$ crontab -l
* * * * 1 /home/oracle/auto_send_tablespace.sh

3.auto_send_tablespace.sh，注意修改$ip项,能否筛选出来服务器IP

#!/bin/bash  
# --------------------------------------------------------------------------+  
#   Author : steven                                                     |   
# --------------------------------------------------------------------------+  
#  ip：注意修改网卡信息，尝试$ip是否能筛选出来ip
# -------------------------------  
#  Set environment here   
# ------------------------------ 
#su - oracle

#创建文件
source /home/oracle/.bash_profile
ip=`/sbin/ifconfig ens160  | awk -F "[ :]+" 'NR==2 {print $3}'`
date_str=`date "+%Y%m%d%H%M"`
dir=/home/oracle/$ip
send_file=$dir/$date_str.txt
if [ ! -d "$dir" ]; then 
        mkdir $dir
        touch $send_file
        else
        touch $send_file
fi

#cd /home/oracle/shell

#查询表空间
echo -e "


表空间统计：" >> $send_file 
sqlplus -s "/ as sysdba" << EOF >> $send_file
SELECT a.tablespace_name "表空间名称", 100-ROUND((NVL(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)", ROUND(a.bytes_alloc/1024/1024,2) "容量(M)", ROUND(NVL(b.bytes_free,0)/1024/1024,2) "空闲(M)", ROUND((a.bytes_alloc-NVL(b.bytes_free,0))/1024/1024,2) "使用(M)" FROM (SELECT f.tablespace_name, SUM(f.bytes) bytes_alloc, SUM(DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes FROM dba_data_files f GROUP BY tablespace_name) a, (SELECT f.tablespace_name, SUM(f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;
exit
EOF

#发送邮件
mail -s "表空间统计：$ip" houzhih@yutong.com < $send_file

#删除30天前的文件
find $dir -name "*.txt" -mtime +30 -exec rm -rf {} ;