KSH script SQL timeout issue

Hi all,

I have a KSH script which is kicking off an sql scripts as follows:

/usr/local/installs/instantclient_10_2/sqlplus -s username/password @$sql_path/sql_query.sql > $tmp_path/sql_query_results

The problem I have is that sometimes the 10g Oracle Database spits out an error saying something along the lines of too many simultaneous logins (this is a shared account and unfortunately I don't administer the Oracle database).

My scripts then sits there for ages until, and I guessing a bit here, some kind of Oracle timeout kicks in.

Is there a way that I can alter my KSH script to only wait say 10 mins for a result, and if it doesn't get one then just exit from the sql query and continue to the next part of my script, or better still attempt the sql query again in another 10 mins?

Many thanks in advance!!!

Not very nice, but you could try and start a child process that sleeps for 10 minutes and then sends a kill signal to its parent. Probably better would be to use a Perl database interface to attempt to make the connection, and only when that's good, to send the SQL and retrieve the results, although this may still suffer from a login jam!

thanks for your response, I think I might have to go for an option like that. As you say not nice but what can I do?!

I would give more emphais on the contents of sql_query.sql . Does it have any commit; rollback statements. Why do you think the script sometimes take a long time. Does it keep waiting for a transactions to commit; May be you should check the other process, locking etc?

What's the exact "ORA-" error message thrown by Oracle 10g in such cases ?

tyler_durden