Oracle Resource Manager Implementation

This is to limit the end user from executing anything that can potentially cause the database slow down with bad queries. 

set serverout on size 5555

--

-- first remove an existing active plan

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';

--

-- delete any existing plan or group

-- we have to create a pending area first

exec dbms_resource_manager.clear_pending_area();

exec dbms_resource_manager.create_pending_area();

exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');

exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');

exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

begin

  dbms_resource_manager.create_pending_area();

  --

  -- we need a consumer group that maps to the desired oracle user:

  dbms_resource_manager.create_consumer_group(

    CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',

    COMMENT=>'This is the consumer group that has limited execution time per statement'

    );

  dbms_resource_manager.set_consumer_group_mapping(

    attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,

    value => 'DSUSER',

    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'

  );

  -- and we need a resource plan:

  dbms_resource_manager.create_plan(

    PLAN=> 'LIMIT_EXEC_TIME',

    COMMENT=>'Kill statement after exceeding total execution time'

  );

  -- now let's create a plan directive for that special user group

  -- the plan will cancel the current SQL if it runs for more than 120 sec

  dbms_resource_manager.create_plan_directive(

    PLAN=> 'LIMIT_EXEC_TIME',

    GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',

    COMMENT=>'Kill statement after exceeding total execution time',

    SWITCH_GROUP=>'CANCEL_SQL',

    SWITCH_TIME=>1,

    SWITCH_ESTIMATE=>false

  );

  dbms_resource_manager.create_plan_directive(

    PLAN=> 'LIMIT_EXEC_TIME',

    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',

    COMMENT=>'leave others alone',

    CPU_P1=>100

  );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

end;

/

exec dbms_resource_manager_privs.grant_switch_consumer_group('DSUSER','GROUP_WITH_LIMITED_EXEC_TIME',false);

exec dbms_resource_manager.set_initial_consumer_group('DSUSER','GROUP_WITH_LIMITED_EXEC_TIME');

select * from DBA_RSRC_CONSUMER_GROUPS;

select * from DBA_RSRC_GROUP_MAPPINGS;

select * from DBA_RSRC_PLANS;

select * from DBA_RSRC_PLAN_DIRECTIVES;

-- to enable it:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';

SELECT se.sid sess_id, co.name consumer_group,

 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time

 FROM v$rsrc_session_info se, v$rsrc_consumer_group co

 WHERE se.current_consumer_group_id = co.id;

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;