| run sql commands in background [message #511318] |
Sat, 11 June 2011 12:08  |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
I am trying to execute sql commands in a shell script and run in the background via crontab. I am providing the input files.
vi test.sql
-----------
spool test.log
select name from v$database;
select name from v$database;
select name from v$database;
spool off
vi script.sh
------------
#! /bin/ksh
sqlplus / as sysdba @test.sql
exit 0
crontab -l
----------
00 01 11 06 * /ora01/script.sh &
The job did not run at 1 pm as I expected. I want to know what's wrong with it.
|
|
|
|
|
|
| Re: run sql commands in background [message #511320 is a reply to message #511319] |
Sat, 11 June 2011 12:20  |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
I modified the script like this :
spool test.log
touch /tmp/launched.txt
select name from v$database;
select name from v$database;
select name from v$database;
spool off
I din't get any output.
|
|
|
|
|
|
|
|
|
|
| Re: run sql commands in background [message #511324 is a reply to message #511318] |
Sat, 11 June 2011 12:34 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
lg123 wrote on Sat, 11 June 2011 18:08
crontab -l
----------
00 01 11 06 * /ora01/script.sh &
The job did not run at 1 pm as I expected.
Since crontab uses 24hr notation that would be 1 am not 1 pm.
|
|
|
|
| Re: run sql commands in background [message #511325 is a reply to message #511324] |
Sat, 11 June 2011 12:43 |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
vi test.sql
-----------
spool test.log
select name from v$database;
select name from v$database;
select name from v$database;
spool off
vi script.sh
------------
#! /bin/ksh
touch /tmp/launched.txt
sqlplus / as sysdba @test.sql
exit 0
crontab -l
----------
40 13 11 06 * /ora01/script.sh &
The file launched.txt got created under /tmp. But I dont find the test.log file which needs to have the output of the sql statments executed.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: run sql commands in background [message #511330 is a reply to message #511329] |
Sat, 11 June 2011 13:42 |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
vi script.sh
------------
#! /bin/ksh
export ORACLE_SID=xxxxxxxx
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
export PATH=$HOME/bin:$PATH
sqlplus / as sysdba @test.sql
exit 0
crontab -l
----------
35 14 11 06 * /ora01/script.sh MAILTO=xxxxxx.xxxxxxxxxxxx@xxxxxxxxxx.com &
I am not getting any output. Even the email option doesn't seem to work.
|
|
|
|
|
|
|
|
|
|
| Re: run sql commands in background [message #511334 is a reply to message #511333] |
Sat, 11 June 2011 14:11 |
lg123
Messages: 225
Registered: August 2008
|
Senior Member |
|
|
capture.env
-----------
HOME=/ora01
LOGNAME=oracle
ORACLE_HOME=/ora01/app/oracle/product/10.2.0
ORACLE_SID=xxxxxxx
PATH=/ora01/bin:/usr/bin:
PWD=/ora01
SHELL=/usr/bin/sh
TZ=US/Eastern
_=/usr/bin/env
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: run sql commands in background [message #511884 is a reply to message #511878] |
Wed, 15 June 2011 13:42 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:I am receiving the output to my email id. But I am not able to view the subject in the email. Subject is blank.
So this has nothing to do with Oracle.
I advise you to put the "mail" command inside the script.
You should better ask this in a Unix forum instead of an Oracle one.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
