#### Tablespace Alert ###############
Crontab Details :
This schecduled scripts will check after every 12 hrs
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
Script Output:
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log1=/u04/MasterDB/scripts/daily_health_check/tb_check.log
printf "to:abc@abc.com\n" > $DR_log1
#printf "cc:core_oradba@mind-infotech.com\n" >> $DR_log1
printf "subject: PRODB Tablespace HOURLY STATUS REPORT\n" >> $DR_log1
#printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log1
sqlplus -s "/as sysdba" <<EOF >> $DR_log1
et echo off
set feed off
set verify off
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
COLUMN TABLESPACE FORMAT A25
select name DBNAME from v\$database;
prompt =======================================================================
prompt TABLESPACE UTILIZATION
prompt =======================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace
/
prompt ========================================================================
prompt Tablespace More Than 85% used
prompt ========================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
set feedback on
set verify on
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/PRODB/ArchiveLog
/
EOF
/usr/sbin/sendmail -t < $DR_log1
oracle@test02:~>