_____________________________________________________________________________________ MANAGING RESOURCES WITH ORACLE DATABASE RESOURCE MANAGER http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN027 ABOUT RESOURCE MANAGER ADMINISTRATION PRIVILEGES http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11860 _____________________________________________________________________________________ EXTERNAL PUBLICATIONS http://www.databasejournal.com/img/DRM_Listing02.html http://www.databasejournal.com/img/DRM_Listing03.html _____________________________________________________________________________________ show parameter resource_manager_plan _____________________________________________________________________________________ set pages 1000 set lines 200 select * from v$rsrc_plan; select name, cpu_managed from v$rsrc_plan; select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE'; select * from dba_rsrc_plans; select distinct plan from dba_rsrc_plans order by 1; select name,to_char(start_time, 'MON DD HH24:MI') start_time,to_char(end_time, 'MON DD HH24:MI') end_time,window_name from v$rsrc_plan_history order by start_time desc; select window_name, resource_plan, active from dba_scheduler_windows where resource_plan is not null and enabled = 'TRUE'; select sid, resource_consumer_group from v$session where resource_consumer_group like 'APP%'; select grantee, granted_group from dba_rsrc_consumer_group_privs order by granted_group; select * from dba_rsrc_consumer_groups; col consumer_group for a25 col comments for a110 select distinct consumer_group, comments from dba_rsrc_consumer_groups order by 1; select consumer_group, comments from dba_rsrc_consumer_groups where status is null order by 1; select * from dba_rsrc_plan_directives; select group_or_subplan from dba_rsrc_plan_directives where plan = 'MIXED_WORKLOAD_PLAN'; select * from dba_rsrc_group_mappings; select * from dba_rsrc_mapping_priority; __________________________________________________________________________________________________ -- CREATE THE RESOURCE MANAGEMENT ADMINISTRATOR ACCOUNT drop user res_mgr cascade; create user res_mgr identified by res_mgr default tablespace users; grant connect, resource to res_mgr; grant select on dba_objects to res_mgr; grant select on dba_rsrc_consumer_groups to res_mgr; grant select on dba_rsrc_plan_directives to res_mgr; grant select on dba_scheduler_windows to res_mgr; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER_PRIVS.grant_system_privilege( grantee_name => 'RES_MGR', privilege_name => 'ADMINISTER_RESOURCE_MANAGER', admin_option => TRUE); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / -- CHECK THE SYSTEM PRIVILEGES FOR RES_MGR col grantee for a10 col privilege for a30 select * from dba_sys_privs where grantee = 'RES_MGR'; -- CREATE RESOURCE CONSUMER GROUP conn res_mgr/res_mgr BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; -- Create the consumer groups DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'APPUSER', mgmt_mth => 'ROUND-ROBIN', comment => 'Application User CG'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / -- CHECK DBA_RSRC_CONSUMER_GROUPS column comments format a60 select consumer_group, comments from dba_rsrc_consumer_groups where consumer_group = 'APPUSER'; select owner, object_type from dba_objects where object_name = 'APPUSER'; -- ASSIGN CONSUMER GROUP 'APPUSER' TO RESOURCEPLAN 'DEFAULT_PLAN' conn res_mgr/res_mgr BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area(); DBMS_RESOURCE_MANAGER.create_pending_area(); -- Assign group to plan DBMS_RESOURCE_MANAGER.create_plan_directive('DEFAULT_PLAN','APPUSER',0,60,0,0,0,0,0,0,NULL); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area(); END; / select * from dba_rsrc_plan_directives where group_or_subplan = 'APPUSER'; -- DROP RESOURCE CONSUMER GROUP conn res_mgr/res_mgr BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area(); DBMS_RESOURCE_MANAGER.create_pending_area(); -- Delete consumer groups DBMS_RESOURCE_MANAGER.delete_consumer_group ( consumer_group => 'APPUSER'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area(); END; / -- DROP THE RESOURCE MANAGEMENT ADMINISTRATOR ACCOUNT conn / as sysdba drop user res_mgr cascade; _____________________________________________________________________________________ DICTIONARY VIEWS ================ Resource Manager Data Dictionary Views -------------------------------------- http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11916 Automated Maintenance Tasks Database Dictionary Views http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12344 _____________________________________________________________________________________