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