oracle表空间内存检查

定时检查oracle表空间内存，发送企业微信。

#!/bin/bash

sqlplus -S "/ as sysdba" <<  EOF
set linesize 200
set pagesize 200
spool /tmp/ora_tablespace2.txt
select a.tablespace_name,to_char(a.bytes/1024/1024/1024,'fm9990.00') "Sum GB",to_char((a.bytes-b.bytes)/1024/1024/1024,'fm9990.00') "used GB",to_char((a.maxbytes-(a.bytes-b.bytes))/1024/1024/1024,'fm990.00') "free_real GB",to_char(round(((a.bytes-b.bytes)/a.maxbytes)*100,2),'fm990.00') as "percent"
from
(select tablespace_name,sum(bytes) bytes  ,sum(case when autoextensible='NO' then bytes else maxbytes end  ) maxbytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
spool off
EOF

wx(){
   curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=***************************' \
   -H 'Content-Type: application/json' \
   -d '
   {
        "msgtype": "text",
        "text": {
            "content": "'${1}'",
        }
   }'
}

TABLE_LIST=`cat /tmp/ora_tablespace2.txt |grep -v TABLESPACE_NAME|grep -v "-"|grep -v rows|awk '{gsub(/[[:space:]]+/,"="); print}'`
arr2=()
for i in ${TABLE_LIST};do
        NAME=`echo $i|cut -d "=" -f 1`
        SIZE=`echo $i|cut -d "=" -f 5`
        DATE=`echo "表空间名称：${NAME}，可扩展表空间使用率：${SIZE}%\n"`
        arr2+=${DATE}
done
wx ${arr2}