News

  1. Home
  2. All posts
All posts, Linux, Oracle, Unix by Team Ajara

Script to get tablespace alert in Oracle

 

Script to get tablespace alert in Oracle

 

This script can be run on any Uinx/Linux flavours.
For AIX,HP, and Linux oratab’s path is /etc/oratab, for solaris oratab’s path is as /var/opt/oracle/oratab.
Threshold value is 100-PCT value .In the below script PCT value mentioned as 5, so threshold value is 95%.
If the tbs used space is crossed 95 % then we will receive mail .

 

Script Name: tbs_check.ksh

#!/bin/ksh
PCT=5
SERVER=`uname -a |awk ‘{print $2}’`
ps -ef|grep pmon|grep -v asm|grep -v grep|awk ‘{print $NF}’ |cut -d_ -f3|while read sid;
do
ORACLE_SID=$sid
export ORACLE_SID
if [ -f /etc/oratab ];
then
orahome=`cat /etc/oratab |sed ‘/^#/d’ |grep -w $ORACLE_SID|cut -d: -f2`
ORACLE_HOME=$orahome
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:.
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib
export LIBPATH
export PATH
else
orahome=`cat /var/opt/oracle/oratab |grep -w $ORACLE_SID|cut -d: -f2`
ORACLE_HOME=$orahome
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:.
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib
export LIBPATH
fi
sqlplus -s “/as sysdba” <<!
set feed off
set linesize 200
set pagesize 200
spool $ORACLE_BASE/scripts/alert/log/tablespace_$sid.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED(MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE(MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL(MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) <$PCT;
spool off
exit
!
if [ `cat $ORACLE_BASE/scripts/alert/log/tablespace_$sid.alert|wc -l` -gt 0 ]
then
mailx -s “`date` Tablespace alert on $sid – $SERVER” dbgrp@ajara.tech < $ORACLE_BASE/scripts/alert/log/tablespace_$sid.alert
fi
done

 

How to run the script  Manually ?

nohup sh tbs_check.ksh >tbs_check_manual.log &

 

Crontab entry:

00,15,30,45  07 * * * /u00/app/oracle/scripts/tbs_check.ksh > /u00/app/oracle/scripts/tbs_check.cron.out

 

See also: