Shell Programming and Scripting

The script checks for free space stats on Oracle. If there are any tablespaces with more than 85% usage it prints the details of the tablespace. If all the tablespaces have more than 15% free space, then "All tablespaces have more than 15 pct free space" must be printed on the screen.

When I run on the shell with ./scriptname it runs as described above. When I run it as a cron job, I do not get the message "All tablespaces have more than 15 pct free space"when all tablespaces have more than 15% free space.

I am pasting a part of the script:

# call Oracle script to query tablespace size
`sqlplus -S "/as sysdba" @/home/sidadm/script/dba_data_files.sql > .tmp1`
# call Oracle script to query freespace in tablespace
`sqlplus -S "/as sysdba" @/home/sidadm/script/dba_free_space.sql > .tmp2`
# paste them into a single file
paste .tmp1 .tmp2 |tail +4 > .tmp

# get the number of tablespaces
TS_CT=`cat .tmp | wc -l`

awk -v m=0 -v n=0 -v i="$TS_CT" '
{
# calculate freespace percentage
k =$3/$2*100;
# print the details if the percentage of freespace is less than 15
if($3/$2*100 < 15)
{
m = m + 1; # m keeps a count of number of tablespaces with freespace percentage less than 15
if(m==1) # print the heading (only once)
{
printf("%s \n","Tablespaces having less than 15pct freespace GB")
printf("%s \n","Tablespace Totalspace Freespace Freespace_pct")
}
len =length ($1)
printf("%s",$1)
for ( ;len < 30 ;len++)
{
printf("%c"," ");
}
len=length($2)
printf("%s",$2)
for ( ;len < 15 ;len++)
{
printf("%c"," ");
}
len = length($3);
printf("%s",$3)
for ( ;len < 15 ;len++)
{
printf("%c"," ");
}

printf("%f \n ",$3/$2*100);

}
else
{
n = n + 1; # n keeps a count of tablespaces with freespace percentage more than 15
if(n==i) # if all the tablespaces have free space more than 15 %
{
printf("%s \n","All tablespaces have more than 15 pct free space")
}
}

}

' .tmp >> $TOBREMAILED

Make sure your environment is the same by sourcing your .profile (wherever your needed variables are set in) etc. when starting it via cron, like

Code:

. ~/.profile

# here comes the script...

If you are still having problems, put a "set -x" in the beginning and check the verbose output of your cronjob.

Also use [ code ] and [ /code ] tags when displaying scripts in the forum please, thank you.

Either you can

- source the .profile before calling the script
eg: * * * * * ./.profile; sh script.sh

- or, call the .profile inside your script.