Script for tablespace free space email alert

#####################################################################################

MONITOR SCRIPT – it will monitor objects close to max extents, tablespace free space

#####################################################################################

#! /bin/sh

TS=`date “+%y%m%d”`

ORACLE_HOME=/d001/oracle/9.2.0.8-64

LD_LIBRARY_PATH=$ORACLE_HOME/lib

ORACLE_TERM=vt100

ORACLE_SID=ORVIT8QA

LOG=/d002/oracle/$ORACLE_SID/utilitys

PATH=/$ORACLE_HOME/bin:$PATH

export ORACLE_HOME ORACLE_SID ORACLE_TERM PATH LOG LD_LIBRARY_PATH

for n in 1

do

if [ $n -eq 1 ]; then

TWO_TASK=brcvita8_ORVIT8QA

DB_NAME=ORVIT8QA

fi

export TWO_TASK

monitor_file=$LOG/$DB_NAME.log

#n=`expr $n + 1`

 rm $monitor_file

echo “Objects close to max extents:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a35;

column type format a10;

column owner format a10;

column extents format 9999;

column max_extents format 9999;

select owner, segment_name, segment_type “TYPE”, extents, max_extents

from sys.dba_segments

where extents/greatest(max_extents,1) >= 0.90

and segment_type not in(‘CACHE’,’ROLLBACK’);

exit;

eof

 echo “Objects that cannot get next extent:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a33;

column type format a8;

column owner format a10;

column tablespace format a13;

column next_extent format 999999999;

select owner, segment_name, segment_type “TYPE”,

tablespace_name “TABLESPACE”, next_extent

from sys.dba_segments b

where segment_type !=’ROLLBACK’ and

next_extent > (select max(bytes) from sys.dba_free_space a

where a.tablespace_name = b.tablespace_name);

exit;

eof

 echo “Tablespaces with low freespace (< 20% & < 250 MB):” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set echo off

set pagesize 30

set feedback off;

drop table system.mon_smt_ts;

create table system.mon_smt_ts as

(select x.tablespace_name, round(sum(x.bytes)/1048576,1) freespace

from sys.dba_free_space x

group by x.tablespace_name);

 drop table system.mon_smt_ts1;

create table system.mon_smt_ts1 as

(select tablespace_name, round(sum(bytes)/1048576,1) availspace

from sys.dba_data_files

group by tablespace_name);

set feedback on;

column “Table Space” format a20

column “Allocated” format a12

column “Free Space” format a12

column “Space Used” format a12

select x.tablespace_name “Table Space”,

to_char(availspace,’99999.9′)||’ Meg’ “Allocated”,

to_char(freespace,’99999.9′)||’ Meg’ “Free Space”,

round(freespace/availspace*100) “Pct Free”,

to_char((availspace-freespace),’99999.9′)||’ Meg’ “Space Used”

from system.mon_smt_ts x, system.mon_smt_ts1 y

where y.tablespace_name = x.tablespace_name

and round(freespace/availspace*100) < 20 and freespace<250

order by round(freespace/availspace*100);

exit;

eof

 null_findings=`egrep “no rows selected” $monitor_file | wc -l`

null_findings=`echo $null_findings`

 if [ “$null_findings” != 3 ]; then

/usr/bin/mailx -s “$DB_NAME Exception Report” dba_ora_offshore@bp.com < $monitor_file

fi

 done