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

Question

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.

Answer 1

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.

Answer 2

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);

Answer 3

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.

Answer 4

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.