Crontab File Execution Problem.

[samirm]

Hi,

I am facing a problem when I am putting the script in crontab. This script basically runs to do a sql query for Oracle Tablespace free - used details. If I execute the same script from shell, it is working and sending the alert.

But if I put that script in crontab, its not working.
How can I debug this ?

Where I am doing the mistake ?


Here is the script
===================

#!/bin/ksh

/oracle/PRD/.profile

sqlplus -s system/system <<EOF > /oracle/PRD/ctsuser/table_space.log

set pages 200

select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;

EOF
addr=`cat $ORACLE_HOME/ctsuser/tbl_space_user.txt`
mailx -r SAP_ORACLE_ADMIN -s "SAP: Oracle Tablespace Daily Report" $addr < /oracle/PRD/ctsuser/table_space.log

==================================
The same script if I execute, the "/oracle/PRD/ctsuser/table_space.log" file is getting generated and sending this as mail to my id.

But if I execute the same script from crontab, "/oracle/PRD/ctsuser/table_space.log" file is making of 0 byte and nothing is coming.

Any idea ?

TIA ..

Sam

 

[samirm]

Also ..

Here is the crontab entry ..

00 07 * * * /oracle/PRD/ctsuser/daily_tbl_space_check.ksh 1>>/tmp/daily_tbl_space_check.log 2>$1


If I change the date in crontab, the /tmp/daily_tbl_space_check.log" is not etting generated also.

 

[columb]

Amend the line

/oracle/PRD/.profile

to

. /oracle/PRD/.profile

Unless you run with the '.' /oracle/PRD/.profile runs in a separate shell and any shell variables which may be set, PATH and ORACLE_HOME for example, will not be set in the calling program.

Ceci n'est pas une signature
Columb Healy

 

[samirm]

Yes columb,

I did that ..I made the ORACLE_HOME, ORACLE_SID info in the script and its working now ..

Thanks ..
Sam