Monitoring Oracle

I wrote these Solaris Unix scripts for a book project about 10 years ago. They are dated but I have used them myself recently to construct useful monitoring scripting in Linux.

Display active dedicated connections as shown below. Execute the command below excluding |wc -l to view connected dedicated server processes.

ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

# ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_
  oracle 24440 24439  0 16:35:42 ?        0:00 oracle<SID> (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Examining Processes

Find all Oracle processes. grep -v grep removes the grep command from the list of processes. Also note that the results of one unix shell command can be passed to the next using the | (pipe)
command.

ps -ef | grep ora_ | grep -v grep

Check the listener and the agent.

ps -ef | grep [lsnr|dbsnmp] | grep -v grep

Below is a command to kill all Oracle processes. Only do this in absolute desperation. Solaris may require the manual removal of semaphores after executing a command like this. Always try a shutdown immediate | abort within svrmgrl and lsnrctl stop | dbsnmp_stop first. The ps -ef command in combination with grep will produce a list of processes named as “ora_”. Application of the $ORACLE_SID variable to the grep function will filter and include all oracle processes containing the currently set Oracle database service identifier. The awk processor will pull the second column of the output, ie. the process ID. The xargs function will allow construction and evaluation of the command kill -9 <PID>. The {} curly braces are generally used to supply xargs with the argument passed from the previous pipe command.

ps -ef | grep ora_ | grep -v grep | grep $ORACLE_SID | awk '{print $2}' | xargs kill -9 {}

Finding Files

I find myself looking for files on a Unix box constantly. There are a number ways to do this using three commands I can think of off the top of my head, which, whereis and find. Both which and whereis require that the file be in the path ($PATH variable). The find command can be used to search for file names recursively through sub-directories as shown below. The . option shown below can be an absolute or relative path.

find . -name "listener.ora" -print

The -print option will display only file names found. However, if this command is executed from the root directory as the oracle user, and obviously the oracle unix user does not have full access to the entire file system, any non-readable directories will be displayed as non-readable in the output. To remove listings of non-readable directories either redirect the results of the file command to another file. Otherwise use a grep command and/or an xargs command to filter and/or re-execute on the find command STDOUT.

The oracle User .cshrc File

Setting Variables

See the .cshrc file in Installing Oracle8i on Solaris in the Oracle User Startup Configuration Files section.

Setting Aliases

Aliases in Unix can allow quick access to otherwise difficult to find directories and can even execute stored commands when applied. Be careful with aliases, some really wild things can be done with them, causing some equally wild results. From an Oracle DBA perspective, it is better to use aliases with respect and perhaps only for quick changes to different directories within the oracle user directory structure. And even then, why would you want to go into directories containing Oracle database datafiles. There is no reason for this. In fact, doing so could cause an error such as deleting a datafile OUCH !!!. Note the inclusion of the alias rm ‘rm -i’. This alias will always prompt prior to deletion.

See the .cshrc file in Installing Oracle8i on Solaris in the Oracle User Startup Configuration Files section.

Execution of Scripts

Executing SQL*PLUS or SVRMGRL within a Script

The short example below will execute a shutdown immediate on the database accessed by the currently set $ORACLE_SID value. Note that if you wish to execute a script such as below as a crontab job you must declare all variables required within the script or execute the .cshrc (.log or .profile) file at the begginning of the execution of each crontab job. Note, when passing scripts as cron-jobs through crontab, crontab cannot see the PATH variable setting. Thus all variables used in scripts must be explicitly set in those scripts.

#!/bin/sh
setenv ORACLE_BASE /<mount-point-1>/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/8.1.7
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib
setenv JAVA_HOME $ORACLE_BASE/jre/1.1.8
setenv ORACLE_SID <SID>

$ORACLE_HOME/bin/svrmgrl<<!
connect internal;
shutdown immediate;
disconnect;
exit
!

Scheduling Jobs with crontab

The scheduled execution of jobs can be handled using what Unix calls a crontab or cron-job. A crontab file can be stored per user, ie. root would have a different crontab file to that of the oracle user for instance. The [-elr] options will allow editing, listing and removal of the user specified crontab file. Note that by default the crontab editor uses a really nasty editor. Set the EDITOR environment variable to the value vi to allow editing of the crontab file with the vi editor.

/bin/sh; setenv EDITOR vi; crontab [-elr] <username>

The crontab entry format is basically <execution-time> <process>. The execution time is entered as minute, hour, day, month of year and day of week as shown below. The * (asterisk) is used as a wildcard to indicate repetition.

minute (0-59)
hour (0-23)
day of the month (1-31)
month of the year (1-12)
day of the week (0-6 with 0=Sunday).

In the example crontab script below two backups, one export and a trace file removal process are executed. The two backups are executed on Monday to Saturday and Sunday at 03h30. The Sunday backup is a more full-backup than the other backup. The export exports the contents of the Oracle USER schema every day. In a large database exporting anything but small things (not data) would be very impractical. In a production environment typically individual tables can be exproted on a daily basis. However, exporting of specific tables and/or Oracle schemas are probably more applicable to development and testing environments. Developers can quite easily drop tables and other database objects in error. Export is a very quick and easy way of restoration for small database objects, particulary not ridiculously large tables. Also note the inclusion of explicit path names. Also note that the inclusion of passwords into crontab executed shell scripts is a possible security risk. However, if a hacker is in that far then the hacker is in already in anyway. Also Oracle password files tend to give complete access to the database if one knows of the existence of the internal user. As far as I know Oracle is trying to phase out the use of the internal, system and sys users. This security issue may be resolved in the future.

30 3 * * 1-6 /<mount-point-1>/oracle/product/8.1.7/sbin/backup.sh <password> <SID> <SID>
30 3 * * 0 /<mount-point-1>/oracle/product/8.1.7/sbin/backup.sh <password> <SID> <SID> All
30 4 * * 0 /<mount-point-1>/oracle/product/8.1.7/sbin/export.sh <password> <SID> <SID> user
30 4 * * 0-6 /<mount-point-1>/oracle/product/8.1.7/sbin/utils/delTraceAudit.sh <SID>

Two files in the etc/cron.d directory are the cron.allow and the cron.deny files. If either or both of the files do not exist then their are no restrictions. Obviously inclusion of a username into either allows or denies cronr-jobs for a user specifically declared in /etc/cron.d/cron.allow and /etc/cron.d/cron.deny respectively.

Running Processes in Background

The nohup command can be used to run processes or scripts in background. What this means is that a process can be executed and the calling shell can be aborted. A crontab job will execute in background. Note that 2>&1 will redirect standard error messages to the standard output device (1 = standard output device and 2 = standard error device). The final & character in the command causes the process to run in background, freeing up the shell for further use.

nohup ./$ORACLE_HOME/sbin/backup.sh temp <SID> <SID> > $ORACLE_HOME/logs/backup.log 2>&1 &

The command nohup ./<shell script> & will send the output to a file called nohup.out.

You may want to watch the execution of the nohup executed process. Do this by viewing the log file as shown below. Note that the tail -f command will display each line as it is appended to a file such as a log file.

more $ORACLE_HOME/logs/backup.log

cat $ORACLE_HOME/logs/backup.log

tail [-20 | -f] $ORACLE_HOME/logs/backup.log

You could even email the results to yourself at an email address. This email address could even be a pager or a cellphone. Pagers and cellphones can be used very effectively to pass problematic or potentially problematic results of a script’s execution requiring action by the DBA. Automated database monitoring and O/S level monitoring can be very effective by utilising email as show below.

nohup $ORACLE_HOME/sbin/backup.sh temp <SID> <SID> > $ORACLE_HOME/logs/backup.log & | mailx -s "backup.log" errors@hostname.com

Disk and File Management

Disk Space

df -k will show mount points and space used.

# df -k
Filesystem            kbytes    used   avail capacity  Mounted on
/proc                      0       0       0     0%    /proc
/dev/dsk/c0t0d0s0    7670973 6310535 1283729    84%    /
fd                         0       0       0     0%    /dev/fd
swap                 1300760     400 1300360     1%    /tmp

The command below could constitute a basis for a disk space running-low warning script.

# df -k | awk '{print $1 " " $5}'
Filesystem capacity
/proc 0%
/dev/dsk/c0t0d0s0 84%
fd 0%
swap 1%

The script below can be used to automate checking of disk space.

#!/bin/ksh

export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME

export host=`hostname`
export email=$1
export USAGE=”$0: Incorrect arguments, Usage: $0 <\”email1 email2 … emailn\”>”

if [ -z “$email” ]; then
echo “$USAGE”
mailx -s “$USAGE” emergencyEMail@whatever.com < /dev/null
exit 1
elif [ `whoami` != ‘root’ ]; then
echo “$0 aborted – user `whoami` is incorrect, must be user root”
mailx -s “$0 aborted – user `whoami` is incorrect, must be user root” $email < /dev/null
exit 1
fi

if [ -z “$PATH” ] || [ -z “$host” ]; then
echo “$0 aborted – variable not defined”
mailx -s “$0 aborted – variable not defined” $email < /dev/null
exit 1
fi

echo “Executing $0 on $host”

panic=95
scream=99

df -k | awk ‘{\
if (($1 != “Filesystem”) && ($1 != “fd”) && ($1 != “/proc”))\
{\
if ($5 > scream) { print “SCREAM !!! – Disk space on”,host,$1,”@”,$5 }\
else if ($5 > panic) { print “Panic – Disk space on”,host,$1,”@”,$5 }\
}\
}’ scream=$scream panic=$panic host=$host > /usr/local/sbin/logs/diskspace.log

if [ -s /usr/local/sbin/logs/diskspace.log ]; then

sub=”Script $0 on $host detected disk space limits exceeded !!!”
echo $sub
mailx -s “$sub” $email < /usr/local/sbin/logs/diskspace.log
exit 1
fi

exit 0

Find and Display Files

ls -lat – list recently touched files.
ls -lac – list recently changed files.
du -s * | sort -n – du -s shows file size only, piping to sort orders the results in ascending order.

# du -s $ORACLE_HOME | sort -n
1801978 /<mount-point-1>/oracle/product/8.1.7

du -sk $ORACLE_HOME – changes number displayed from units of 512 bytes to 1024 bytes.

# du -sk $ORACLE_HOME
900989  /<mount-point-1>/oracle/product/8.1.7

find . -print | xargs grep -i oracle – finds lines in files, in the current directory recursively, which have the word oracle in them.

find . -mtime -1 -print – files created or altered today.

find . -size +1048576c -print – files greater than 1Mb.

find . -mtime +7 -exec rm {} \; – remove files in the current directory created more than 7 days ago.

Backups Using the tar Utility

Compress <file> with tar cvf <file> | gzip <file> and decompress with gzip -d <file>.tar.gz | xargs tar xvf {}.

Delete Old Trace and Audit Files

#!/bin/ksh

export ORACLE_BASE=/<mount-point-1>/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME

export host=`hostname`
export email=$1
export USAGE="$0: Incorrect arguments, Usage: $0 <\"email1 email2 ... emailn\">"

if [ -z "$email" ]; then
echo "$USAGE"
mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
exit 1
elif [ `whoami` != 'oracle' ]; then
echo "$0 aborted - user `whoami` is incorrect, must be user root"
mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
exit 1
fi

if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]; then
echo "$0 aborted - variable not defined"
mailx -s "$0 aborted - variable not defined" $email < /dev/null
exit 1
fi

echo "Executing $0 on $host"

echo Cleaning trace and transaction audit files for $1 upto 14 days ago
unalias rm
find $ORACLE_BASE/admin/$1/bdump/*.trc -mtime +14 | xargs rm -f
find $ORACLE_BASE/admin/$1/udump/*.trc -mtime +14 | xargs rm -f
find $ORACLE_BASE/admin/$1/cdump/*.* -mtime +14 | xargs rm -f
find $ORACLE_HOME/rdbms/audit/*.aud -mtime +14 | xargs rm -f
alias rm 'rm -i'

File Permissions and Ownership

Change file permissions with the chmod command and change file ownership with the chown command.

Making Sure the Database is Running

#!/bin/ksh

export ORACLE_BASE=/<mount-point-1>/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_SBIN=$ORACLE_HOME/sbin
export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME

export host=`hostname`
export email=$1
export password=$2
export ORACLE_SID=$3
export tnsname=$4
USAGE="$0: Incorrect arguments, Usage: $0 <email> <password> <sid> <tnsname>"

if [ -z "$email" ]; then
echo "$USAGE"
mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
exit 1
elif [ `whoami` != 'oracle' ]; then
echo "$0 aborted - user `whoami` is incorrect, must be user root"
mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
exit 1
fi

if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]\
|| [ -z "$password" ] || [ -z "$ORACLE_SID" ] || [ -z "$tnsname" ]; then
echo "$0 aborted - variable not defined"
mailx -s "$0 aborted - variable not defined" $email < /dev/null
exit 1
fi

echo "Executing $0 on $host"

#check processes
pmon="`ps -eaf | grep -i pmon | grep -i ora_ | grep -v grep | wc -l`"
smon="`ps -eaf | grep -i smon | grep -i ora_ | grep -v grep | wc -l`"
dbwr="`ps -eaf | grep -i dbw | grep -i ora_ | grep -v grep | wc -l`"
lgwr="`ps -eaf | grep -i lgwr | grep -i ora_ | grep -v grep | wc -l`"
ckpt="`ps -eaf | grep -i ckpt | grep -i ora_ | grep -v grep | wc -l`"
reco="`ps -eaf | grep -i reco | grep -i ora_ | grep -v grep | wc -l`"
arch="`ps -eaf | grep -i arc | grep -i ora_ | grep -v grep | wc -l`"
processes=`echo "$pmon+$smon+$dbwr+$lgwr+$ckpt+$reco+$arch" | bc`

if [ $processes -eq 0 ]; then
echo Aborting - Database process ERROR
echo pmon=$pmon,smon=$smon,dbwr=$dbwr,lgwr=$lgwr,ckpt=$ckpt,reco=$reco,arch=$arch
mailx -s "Aborting - Database process ERROR" $email > /dev/null
exit 1
fi

#check listener
listener="`ps -eaf |grep lsnr |grep -v grep | wc -l`"
if [ $listener -eq 1 ]; then
lsnr_SIDs="`lsnrctl status | grep $2 | grep -v grep | wc -l`"
if [ $lsnr_SIDs -eq 0 ]; then
echo Aborting - Listener configuration does not match SID parameter
mailx -s "Aborting - Listener configuration does not match SID parameter" $email > /dev/null
exit 1
fi
else
i=0
while [ $listener -eq 0 ]; do
if [ $i -gt 1 ]; then
echo Aborting - Listener is DOWN - failed to restart
echo Check $TNS_ADMIN/listener.ora configuration
mailx -s "Aborting - Listener is DOWN - failed to restart" $email > /dev/null
exit 1
fi
lsnrctl start
listener="`ps -eaf |grep lsnr |grep -v grep | wc -l`"
i=`echo "$i+1" | bc`
done
fi

#check database state

$ORACLE_HOME/bin/sqlplus $3<<!
connect internal;
set termout off echo off feed off trimspool on head off pages 0;
spool $ORACLE_SBIN/logs/databaseAlive.log;
select name from v$database;
select open_mode from v$database;
spool off;
disconnect;
exit;
!

dbState="`cat $ORACLE_SBIN/logs/databaseAlive.log |grep READ |grep -v grep | wc -l`"
if [ $dbState -eq 0 ]; then
echo Aborting - database for SID $2 not open
mailx -s "Aborting - database for SID $2 not open" $email > /dev/null
exit 1
fi

ORA-600 Errors in the Alert Log

The script below will detect and email ORA-00600 errors in the Oracle database alter log file.

#!/bin/ksh

export ORACLE_BASE=/<mount-point-1>/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_SBIN=$ORACLE_HOME/sbin
export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME

export host=`hostname`
export email=$1

USAGE="$0: Incorrect arguments, Usage: $0 <email>"

if [ -z "$email" ]; then
echo "$USAGE"
mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null
exit 1
elif [ `whoami` != 'oracle' ]; then
echo "$0 aborted - user `whoami` is incorrect, must be user root"
mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null
exit 1
fi

if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]\
|| [ -z "$password" ] || [ -z "$ORACLE_SID" ] || [ -z "$tnsname" ]; then
echo "$0 aborted - variable not defined"
mailx -s "$0 aborted - variable not defined" $email < /dev/null
exit 1
fi

echo "Executing $0 on $host"

err="`tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep | wc -l`"

if [ $err -gt 0 ]; then

tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep > $ORACLE_SBIN/logs/ora600.log
sub="Script $0 on $host detected ORA-00600 for SID $ORACLE_SID"
echo $sub
mailx -s "$sub" $email < $ORACLE_SBIN/logs/ora600.log
exit 1

fi

exit 0

Tablespace Size Checking

This script is intended for a database with non-AutoExtensible tablespace databases. The highlighted sections apply to a standby database only.

#!/bin/ksh

ORACLE_BASE=/<mount-point-1>/oracle
ORACLE_HOME=$ORACLE_BASE/product/8.1.7
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib
JAVA_HOME=$ORACLE_BASE/jre/1.1.8
TNS_ADMIN=/<mount-point-1>/oracle/product/8.1.7/network/admin
ORACLE_SID=<SID>
ORACLE_DBF1=/<moint-point-1>/oracle/oradata/$ORACLE_SID
ORACLE_DBF2=/<moint-point-2>/oracle/oradata/$ORACLE_SID
ORACLE_BACKUPS=/<moint-point-2>/backups
ORACLE_SBIN=$ORACLE_HOME/sbin
ORACLE_ALERT=$ORACLE_BASE/admin/$ORACLE_SID/bdump
PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin

host=`hostname`

if [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]\
|| [ -z "$TNS_ADMIN" ] || [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_DBF1" ] || [ -z "$ORACLE_DBF2" ] || [ -z "$ORACLE_BACKUPS" ]\
|| [ -z "$ORACLE_SBIN" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$PATH" ] || [ -z "$host" ]; then
echo "$0 aborted - variable not defined"
mailx -s "$0 aborted - variable not defined" $email < /dev/null
exit 1
fi

USAGE="$0: Incorrect arguments, Usage: $0 <\"email1 email2 ... emailn\">"

if [ `whoami` != 'oracle' ]; then
echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
exit 1
elif [ -z "$1" ]; then
echo "$USAGE"
mailx -s "$USAGE" name@xyz.com < /dev/null
exit 1
fi

email=$1

echo "Executing $0 on $host"

panic=95
scream=99

$ORACLE_HOME/bin/svrmgrl<<!
connect internal;
alter database open read only;
exit
!

$ORACLE_HOME/bin/sqlplus system/<password><<!
set term off echo off feedback off show off trim off trims off verify off linesize 132;
spool $ORACLE_SBIN/logs/tablespace.log;
SELECT 'Tablespace '||df.tablespace_name "TBS"
,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
,round(((df.bytes - sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used"
,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
,round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
,df.autoextensible "AutoExtensible"
FROM dba_free_space fs,
dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
ORDER BY df.file_id;
SELECT round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free"
,round(sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free"
,sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM dba_free_space fs,
dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible
ORDER BY df.file_id;
spool off;
exit
!

$ORACLE_HOME/bin/svrmgrl<<!
connect internal;
shutdown immediate;
startup nomount;
alter database mount standby database;
exit
!

cat $ORACLE_SBIN/logs/tablespace.log | grep Tablespace | grep -v grep | grep -v SQL\> | awk '{\
if (($7 == "NO") && (int($4) > int(scream)))\
{\
print "SCREAM !!! - Non-AutoExtensible Tablespace",$2,"space in database",sid,"on",host,"@",$4"%"\
}\
else if (($7 == "NO") && (int($4) > int(panic)))\
{\
print "Panic - Non-AutoExtensible Tablespace",$2,"space in database",sid,"on",host,"@",$4"%"\
}\
}' scream=$scream panic=$panic host=$host sid=$ORACLE_SID > $ORACLE_SBIN/logs/dbfspace.log

#else if (int($4) > int(scream)) { print "SCREAM !!! - Tablespace",$2,"space in database",sid,"on",host,"@",$4"%" }\
#else if (int($4) > int(panic)) { print "Panic - Tablespace",$2,"space in database",sid,"on",host,"@",$4"%" }\

if [ -s $ORACLE_SBIN/logs/dbfspace.log ]; then
sub="Script $0 on $host in database $ORACLE_SID detected tablespace limits exceeded !!!"
echo $sub
mailx -s "$sub" $email < $ORACLE_SBIN/logs/dbfspace.log
exit 1
fi

exit 0