Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘Script for tablespace free space email alert’

Script for tablespace free space email alert

Posted by Pavan DBA on March 9, 2012


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

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

Posted in Administration, Scripts | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: