Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 28 '13 at 8:19

This question came from our site for professional and enthusiast programmers.

    
Do you invoke your SQL scripts with SQL*Plus within your shell scripts? –  Yasir Arsanukaev May 28 '13 at 7:53
    
I wouldn't kill them from shell script, read this AskTom post –  A.B.Cade May 28 '13 at 8:01
    
Do your scripts must be run in sequence, one after another, or you can run them concurrently? Also do you run SQL*Plus on the live database host, or you do it from a different host? Do you have another host with database instance from which you can run your script? –  Yasir Arsanukaev May 29 '13 at 14:36

4 Answers 4

up vote 0 down vote accepted

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.

share|improve this answer
    
This is exactly what I'm after, thank you very much. Unfortunately I cannot upvote your answer. –  cardybean Jun 5 '13 at 10:23
    
You're welcome. If you accept this answer (by clicking the check below the up/down vote) it will give you a slight rep bump, almost enough to up-vote. –  Leigh Riffel Jun 5 '13 at 14:20
    
Ah yes, I have checked the answer but still require 15 reputation to upvote. –  cardybean Jun 24 '13 at 11:57
    
By the way I did solve the problem, and it was much easier than I expected. You set SQLPlus away on low priority with nice -n +19 s.sh, record the PID into var with $!, then sleep for the max amount of time you want the SQLPlus session to run, then grep ps -ef for the PID, kill it if it exists. –  cardybean Jun 24 '13 at 12:07

In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources.

Usually your workflow is as follows:

  1. Create a resource plan
  2. Create the resource consumer groups
  3. Create the resource consumer group mappings
  4. Create the resource-plan directives

You create a plan with the DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure. Plan name is the only required parameter:

DBMS_RESOURCE_MANAGER.CREATE_PLAN('myplan');

Then you create the resource consumer groups as needed with DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP:

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'limit_5_min'
  , comment => 'users with 5 minute execution time limit');

After the necessary resource consumer groups are created, you can define resource consumer group mappings with DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING based on the session attribute so that the new and existing sessions are automatically assigned to appropriate resource consumer groups:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER
  , value => 'spongebob'
  , consumer_group => 'limit_5_min');

This mapping will assign all the session made from the OS user spongebob to the resource consumer group created earlier. There are a number of other attributes you can use to map the session to consumer groups, for example, ORACLE_USER. See DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure parameters table for a full list of available attributes.

Now is the most important part. You will create the resource-plan directives that will govern the resource usage by the users. You use the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure to create the directives. Let's create the directive which limits the session execution time:

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'myplan'
  , group_or_subplan => 'limit_5_min'
  , switch_time => 300
  , switch_group => 'KILL_SESSION');

This directive means that every session created from the OS user spongebob and executes more than 300 seconds (5 minutes) will be automatically killed by DRM.

You will also need to grant the users privileges to switch to the appropriate resource consumer groups with DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure, otherwise the mapping will not work:

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    grantee_name => 'scott'
  , consumer_group => 'limit_5_min'
  , grant_option => false);
share|improve this answer
    
In the same vein, you could use CLIENT_MACHINE mapping attribute value to determine from which machine the connection is created to the live database to assign the session to the appropriate consumer group. See DBMS_RESOURCE_MANAGER Constants table to learn more about the available attributes. –  Yasir Arsanukaev May 28 '13 at 8:55
    
Quite forgot to say that the resource plan can be enabled with RESOURCE_MANAGER_PLAN initialization parameter or by using Scheduler Windows. Read "Enabling Oracle Database Resource Manager and Switching Plans" for more info. –  Yasir Arsanukaev May 28 '13 at 9:14
    
Thanks a lot for the very informative answer :). However, this is a pretty big live service (nation wide) and I will not have the appropriate privs to start playing the resource manager. Ideally, I want to kill the SQL script from within my shell script. Is this something you'd know how to do? –  cardybean May 28 '13 at 11:03

Yasir's answer is excellent if you are on Enterprise Edition. If you are on standard edition here is an alternative that will work if you can use a different user for the connection. Create a profile for the new user that limits the session connect time. If the time is exceeded, the session will be killed. Because the database doesn't check continuously, the cutoff could be delayed a bit. Also you'll need to have RESOURCE_LIMIT set to true in your system parameters.

share|improve this answer

If you have another Oracle instance running on different host, from which you can run your script, you could employ Oracle Scheduler and Oracle Database Resource Manager to control run time.

You would create external jobs and programs (which have type EXECUTABLE) to run scripts outside the database from the command line and assign these jobs to different job classes to limit run time with resource consumer groups as explained in my other answer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.