Managing Resources

Create Resource Management

Usefull tables:
1. v$session column "resource_consumer_group"
2. dba_rsrc_consumer_groups
3. dba_rsrc_consumer_group_privs
4. dba_rsrc_group_mappings
5. dba_rsrc_plan_directives
6. dba_rsrc_plans

In oracle database,there exists package dbms_resource_manager which helps for managing resources as the name says itself.
For the first time you should create pending area,in which resource management objects should be defined and validated before they are activated.

--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--create resource consumer groups
*Note: There exist 3 parameters that can be defined during group creation:
1.CONSUMER_GROUP->name of the group
2.COMMENT
3.CPU_MTH->ROUND_ROBIN(default,fair distribution),RUN_TO_COMPLETION(most active sessions are ahead)
SQL>exec dbms_resource_manager.create_consumer_group(consumer_group => 'session_limit',comment => 'limit group');

--map user in that group
SQL>exec dbms_resource_manager.set_consumer_group_mapping(attribute => 'CLIENT_OS_USER',value=> 'TEST_USER', consumer_group => 'session_limit');
*Note:There exists other options,not just for users,but also for : CLIENT_PROGRAM, CLIENT_MACHINE,ORACLE_USER...

--create plan
SQL>exec dbms_resource_manager.create_plan('session_limit_plan',comment => 'limit plan');

--create plan directive for special consumer group
SQL>exec dbms_resource_manager.create_plan_directive(plan => 'session_limit_plan', group_or_subplan => 'session_limit',comment => 'Limit Session time',switch_group => 'kill_session',switch_time => 60 );
*Note:In this case session assigned to that group will have execution time limit 60sec and after exceeding this number session will be killed(You can assign following values also:CANCEL_SQL,another group name)

It is essential to create plan directive for OTHER_GROUPS(group which is assigned to the object when there exists no active group for that object)

--create plan directive for other_groups
SQL>exec dbms_resource_manager.create_plan_directive(plan=> 'session_limit_plan',group_or_subplan => 'OTHER_GROUPS',comment=> 'no comment');

--validate pending area(submit also validates but for debugging it is better to validate before submitting )
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area(validates,submits,clears pending area)
SQL>exec dbms_resource_manager.submit_pending_area;

--enable resource manager
SQL>alter system set resource_manager_plan = 'session_limit_plan' scope = both;
*Note: if you see v$session view like that:
SQL>select s.resource_consumer_group
from v$session s
where s.username='TEST_USER'
You will see that the value is OTHER_GROUPS, to switch to our desired group,follow these steps:
-----------------------------------Start of Variant 1------------------------------------------
--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--grant switching
SQL>exec
dbms_resource_manager_privs.grant_switch_consumer_group('test_user','session_limit',false);

--setting initial group
SQL>exec dbms_resource_manager.set_initial_consumer_group('test_user','session_limit');

--validate pending area
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area
SQL>exec dbms_resource_manager.submit_pending_area;
-----------------------------------End of Variant 1------------------------------------------

-----------------------------------Start of Variant 2------------------------------------------
--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--switch between consumer
SQL>exec dbms_resource_manager.switch_consumer_group_for_sess (session_id => '134', session_serial=> '4699', consumer_group => 'session_limit');

--validate pending area
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area
SQL>exec dbms_resource_manager.submit_pending_area;
-----------------------------------End of Variant 2------------------------------------------

Delete Resource Management

--deactivate resource manager
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='' scope = both;

--create pending area
SQL>exec dbms_resource_manager.create_pending_area();

--delete plan
SQL> exec dbms_resource_manager.DELETE_PLAN ('SESSION_LIMIT_PLAN');

--delete consumer group
SQL> exec dbms_resource_manager.delete_consumer_group(consumer_group=>'SESSION_LIMIT');

--submit pending area
SQL> exec dbms_resource_manager.submit_pending_area;