Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?
In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources.

Usually your workflow is as follows:


Create a resource plan
Create the resource consumer groups
Create the resource consumer group mappings
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 =&gt; 'limit_5_min'
  , comment =&gt; '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 =&gt; DBMS_RESOURCE_MANAGER.CLIENT_OS_USER
  , value =&gt; 'spongebob'
  , consumer_group =&gt; '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 =&gt; 'myplan'
  , group_or_subplan =&gt; 'limit_5_min'
  , switch_time =&gt; 300
  , switch_group =&gt; '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 =&gt; 'scott'
  , consumer_group =&gt; 'limit_5_min'
  , grant_option =&gt; false);