Crontab - Daily Tablespace Check output on mail

#### 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:~>