Shell: How to time a script running in SQLPlus and kill it after x amount of minutes

oraclesqlplusunix

I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I don't want them to run for over 5 minutes.

Is there a way to – within my shell script – time the script's run time and kill it after 5 minutes?

Thanks in advance.

Best Answer

If you must kill the session from within the script, then here is a method I would investigate.

Create a table to store the run status. It need only have one column for an SID. The first thing your script would do is start a second instance of itself running in the background and passing it a parameter to indicate that it is the background process. The background branch of the script would sleep for the maximum amount of time you want the process to run and when it wakes up it would check the table and if there is an SID stored will kill the session and end. In parallel to this, the original/foreground process after kicking off the background version of itself would create a session, insert it's SID into the new table and commit. It could then run all the statements necessary and end by deleting it's SID from the table.

If the script takes less than the allotted time, then the background process will have nothing to do when it wakes up. If the script takes too long the background process will kill the foreground session.

In theory this seems like it would work, but I haven't tried it.

Related Solutions

Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem

You really need to generate an explain plan. Since your query has an OR there may be situations where the query is being short circuited based on data not existing, hence the performance is not consistent. Also, don't join everything together, subqueries with low cardinality put at the top in a WITH subquery_name clause, and join similar tables together, then join them with other tables.

Db2 – How to use a variable date when running a db2 command in a shell script

Shell quotes typically delimit arguments or escape other special shell characters from shell interpretation. You can contain (and therefore escape) different kinds of shell-quotes within other kinds based on various rules:

"''''" - a soft-quoted string can contain any number of hard-quotes.
"\"" - a \ backslash can escape a "soft-quote within a "soft-quoted string.
In this context a \\backslash also escapes itself, the \$expansion token, and \newlines as noted below, but is otherwise treated literally.
"${expand} and then some" - a soft-quoted string can contain an interpreted shell $expansion.
'"\' - a 'hard-quoted string can contain any character other than a 'hard-quote.
\ - an unquoted backslash will escape any following character for literal interpretation - even another backslash - excepting a \newline.
In a \\newline case both the \ backslash and the \newline are completely removed from the resulting interpreted command. ${parameter+expand "$parameter"} - quotes resulting from a shell expansion almost never serve as delimiter markers excepting a few special cases. I won't venture to describe these further here.

Test:

root@onare:/home/onare# echo $day0_T1
2015-07-16 14:10:44
root@onare:/home/onare# db2="something '${day0_T1}'"
root@onare:/home/onare# echo $db2
something '2015-07-16 14:10:44'
root@onare:/home/onare#

Best Answer

Related Solutions

Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem

Db2 – How to use a variable date when running a db2 command in a shell script

Related Question