Shell Script to Execute Oracle Sql Script in Crontab for a Specified SID

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
For general shell script to execute SQL statements does not require the environment as those required parameter might be set already for the sessions. But when it comes to Crontab job, script will run independently from other sessions and it needs all the required parameter to be set inside the script.
What are the required parameters to run a SQL in a Crontab shell script?

1.   Oracle SID
export ORACLE_SID=
2.   Oracle Base(optional) & Oracle Home
export ORACLE_BASE=
export ORACLE_HOME=
3.   Set the Path for sqlplus executable or directly call the sqlplus from ORACLE_HOME/bin directory.
e.g: export PATH=/u00/app/oracle/product/11.2.0.3/db_1/bin:.
Or
Call sqlplus with absolute path
/u00/app/oracle/product/11.2.0.3/db_1/bin/sqlplus
Example
Create a sql script with the commands to be executed.
$ vi gath.sql
set echo on
set feedback on
set timing on
set time on
select name from v$database;

Create a shell script with required parameters
$ vi sql.sh
#!/bin/bash
export ORACLE_SID=transprd
export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=/u00/app/oracle/product/11.2.0.3/db_1
export PATH=/u00/app/oracle/product/11.2.0.3/db_1/bin:.
sqlplus scott/tiger << EOF > /home/oracle/st/gath.log
@/home/oracle/st/gath.sql
exit
EOF

Add a crontab entry using crontab –e command with required execution timings.
14 11 * * * /home/oracle/st/sql.sh > /home/oracle/st/cron.log


_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer