tablespace monitoring script

Shell Programming and Scripting
1

I have prepared the below script to monitor the tablespace and alert the users whenever it reaches a threshold limit.

#!/bin/sh
. /home/.profile

sqlplus -s $LOGON << .eof > $scripts/check_tablespace.temp
set pages 0
select tablespace_name, free_percent
from (
            SELECT b.tablespace_name, b.tablespace_size_mb, sum(nvl(fs.bytes,0))/1024/1024 free_size_mb,
            (sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100) free_percent
            FROM dba_free_space fs,
                 (SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb FROM dba_data_files
                  GROUP BY tablespace_name
                 ) b
           where
           fs.tablespace_name like 'INFA_%'
           and
           fs.tablespace_name = b.tablespace_name
           group by b.tablespace_name, b.tablespace_size_mb
        ) ts_free_percent
WHERE free_percent < 60
ORDER BY free_percent;
exit
eof

cd $scripts
chmod ugo+rw check_tablespace.temp
counter=`cat check_tablespace.temp | sed '/^$/d'| wc -l`
echo "count="$counter
if [ $counter -gt 0 ] then
   cat check_tablespace.temp | mailx -s 'Alert: Free tablespace alert' $alertlist
fi
rm check_tablespace.temp

But when I execute the script, it fails with following error.

cat: cannot open check_tablespace.temp

Script creates check_tablespace.temp and when I execute the cat command from commandline, it works fine.

cat check_tablespace.temp
INFA_TS                        45.575

Also i tried to change the permissions assuming that it is a permission problem, but still it fails with the same error.

Sam

2

Hi.

What is the value of "scripts"?

If it's not set, the file will go into the root directory, and the cd will go to your home directory.

i.e

$scripts/check_tablespace.temp

becomes

/check_tablespace.temp

and

cd $scripts

becomes

cd
3

Thanks for the reply. The value of $scripts is set at .profile which i am sourcing it during the initial part of the script

. /home/.profile
4

What is the dot here? The closing one should be the same, no?

sqlplus -s $LOGON << .eof > ...
...
.eof
5

Yes thats a good catch. I removed the dot (.eof) from eof and added ';' in the if statement. It worked.

Thanks much

Sam