Oracle Database Resource Manager

begin
--
-- Prevent ORA-29370: pending area is already active:
--
-- dbms_resource_manager requires the corresponding privileges
--
   dbms_resource_manager.clear_pending_area;
--
   dbms_resource_manager.create_pending_area;
end;
/

select * from dba_rsrc_categories where status = 'PENDING';

begin
   dbms_resource_manager.create_consumer_group(
        consumer_group   => 'limit_exec_time_grp',
        comment          => 'Don''t let users run statements for longer than a given amoun of time'
   );
end;
/

select * from dba_rsrc_consumer_groups where lower(consumer_group) = 'limit_exec_time_grp';

begin
   dbms_resource_manager.create_plan(
        plan             => 'limit_exec_time_pln',
        comment          => 'Kill a statement when it exceeds the permitted execution time'
   );
end;
/

select
   pln.num_plan_directives,
   pln.*
from
   dba_rsrc_plans pln
where
   lower(pln.plan) = 'limit_exec_time_pln';

begin
   dbms_resource_manager.create_plan_directive(
        plan             => 'limit_exec_time_pln',
        group_or_subplan => 'limit_exec_time_grp',
        switch_time      =>  3                   , -- After three seconds, switch to 
        switch_group     => 'CANCEL_SQL'         , -- CANCEL_SQL, which is one of the predefined resource manager consumer groups
        switch_estimate  =>  false               ,
        switch_for_call  =>  true                ,
        comment          => 'Kill a statement when it exceeds the permitted execution time'
   );
end;
/

select * from dba_rsrc_plan_directives where lower(plan) = 'limit_exec_time_pln';

begin
--
-- Fallback for non-impacted users.
-- OTHER_GROUPS needs to be included in each top plan,
-- otherwise, validate_pending_area throws
--   ORA-29377: consumer group OTHER_GROUPS is not part of top-plan
--
   dbms_resource_manager.create_plan_directive(
        plan             => 'limit_exec_time_pln',
        group_or_subplan => 'OTHER_GROUPS',
        comment          => 'Don''t impact others'
   );
end;
/

begin
   dbms_resource_manager.validate_pending_area;
   dbms_resource_manager.submit_pending_area;
end;
/

begin
   dbms_resource_manager_privs.grant_switch_consumer_group(
        grantee_name     => 'RENE',
        consumer_group   => 'limit_exec_time_grp',
        grant_option     =>  false
   );

end;
/

select * from dba_rsrc_consumer_group_privs where granted_group = 'LIMIT_EXEC_TIME_GRP';

begin
   dbms_resource_manager.set_initial_consumer_group(
        user             => 'RENE',
        consumer_group   => 'limit_exec_time_grp');
end;
/

select
   usr.initial_rsrc_consumer_group
from
   dba_users usr
where
   usr.username = 'RENE';

alter system
      set resource_manager_plan = limit_exec_time_pln
      scope                     = memory;

alter session set events 'immediate crash';
connect rene/rene

select
   username,
   resource_consumer_group 
from
   v$session
where
   sid = sys_context('userenv', 'sid');

select count(*) from dba_objects, dba_objects, dba_objects, dba_objects;

begin
   dbms_session.sleep(10);
end;
/

with
   function f(s integer) return integer is
   begin
      dbms_session.sleep(s);
      return s;
   end f;
select f(2) from dual union all
select f(3) from dual union all
select f(4) from dual
/

alter system
      set resource_manager_plan = ''
      scope                     = memory;

begin
   dbms_resource_manager.create_pending_area;
   dbms_resource_manager.delete_plan('limit_exec_time_pln');
   dbms_resource_manager.delete_consumer_group('limit_exec_time_grp');
   dbms_resource_manager.validate_pending_area;
   dbms_resource_manager.submit_pending_area;
end;
/

begin
   dbms_resource_manager.switch_consumer_group_for_sess (
      session_id     => to_number(substr(dbms_session.unique_session_id, 1, 4),'XXXX'),
      session_serial => to_number(substr(dbms_session.unique_session_id, 5, 4),'XXXX'),
      consumer_group =>'limit_exec_time_grp'
   );
end;
/

select * from v$rsrc_consumer_group;
select * from v$rsrc_plan;
select * from v$rsrc_plan_history;
select * from v$rsrc_session_info;