#!/bin/bash
source ~/.bash_profile




file=/tmp/daily_check.log
sqlplus -s ' / as sysdba' <<eof
set pages 999
set linesize 500


col FILE_ID format 999
col TABLESPACE_NAME format a20
col FILE_PATH format a40
col AUTOEXTENSIBLE format a5
col MAX_M format 999999
col TOTAL_SPACE format a20
col USED_SPACE format a20
col REMIND_SPACE format a20
col REMIND_RATE format a20




spool d_ck.log


--tablespace free
select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total ,sum(MAXBYTES/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name and round((b.free/a.total)*100)<6
order by "% Free";




--datafile free
select b.file_id FILE_ID,
b.tablespace_name TABLESPACE_NAME ,
b.AUTOEXTENSIBLE,b.MAXBYTES/1024/1024 MAX_M,b.file_name FILE_PATH,
ROUND(b.bytes/1024/1024,2) ||'M'  TOTAL_SPACE,
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024,2)||'M' USED_SPACE,
ROUND(sum(nvl(a.bytes,0))/1024/1024,2)||'M' REMIND_SPACE,
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' REMIND_RATE
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES/1024/1024
having ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)<0.06
order by 7;


spool off
exit
EOF




date >$file
echo "###########################" >>$file


echo "." >>$file
echo "." >>$file
echo "Tablespace and Tatafile Space Information" >>$file
echo "###########################" >>$file
cat d_ck.log >> $file




echo "." >>$file
echo "." >>$file
echo "OS Disk Space Information" >>$file
echo "###########################">>$file
df -hP|column -t >>$file
cat $file|mailx -s "MESDB 192.168.130.23 SPACE CHECK" XX@YY.com
</eof