ORACLERESOURCEMANAGER资源管理器
本文演示简单资源计划的使用，普通资源计划的使用请参考oracle官方文档1、简单资源计划以SYS_GROUP组作为level1，OTHER_GROUPS组作为level3,不需要单独创建组（CREATE_CONSUMER_GROUP）和计划指令（CREATE_RESOURCE_PLAN_DIRECTIVES）
2、如果要指定用户的默认组（DBA_USERS.INITIAL_RSRC_CONSUMER_GROUP），dba用户必须调用DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP为指定用户赋于切换到指定组的权限。
演示示例
SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> --1.创建简单资源计划SQL> BEGIN2 DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',3CONSUMER_GROUP1 => 'mailgrp', GROUP1_CPU => 80,4CONSUMER_GROUP2 => 'mgrgrp', GROUP2_CPU => 20);5 END;6 /PL/SQL 过程已成功完成。SQL> --2.为组和用户指定映射规则SQL> BEGIN2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();3 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING4 (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'mail', 'mailgrp');5 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING6 (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'mgr', 'mgrgrp');7 END;8 /PL/SQL 过程已成功完成。SQL> --3.提交变更SQL> BEGIN2 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();3 END;4 /PL/SQL 过程已成功完成。SQL> set linesize 200SQL> COL PLAN FOR A20;SQL> COL CPU_METHOD FOR A20;SQL> COL STATUS FOR A10;SQL> COL MANDATORY FOR A10;SQL> COL COMMENTS FOR A50;SQL> SELECT T.PLAN,T.CPU_METHOD,T.STATUS,T.MANDATORY,MENTS FROM DBA_RSRC_PLANS T;PLAN CPU_METHOD STATUSMANDATORY COMMENTS-------------------- -------------------- ---------- ---------- --------------------------------------------------SIMPLE_PLAN1 EMPHASIS NO Simple planINTERNAL_PLAN EMPHASIS YES Default PlanINTERNAL_QUIESCEEMPHASIS YES Plan to internally quiesce systemSYSTEM_PLANEMPHASIS NO Plan to give system sessions prioritySQL> COL PLAN FOR A20;SQL> COL GROUP_OR_SUBPLAN FOR A20;SQL> COL TYPE FOR A20;SQL> COL CPU_P1 FOR 99999SQL> COL CPU_P2 FOR 99999SQL> COL CPU_P3 FOR 99999SQL> COL MANDATORY FOR A10;SQL> COL COMMENTS FOR A50;SQL> SELECT T.PLAN,T.GROUP_OR_SUBPLAN,T.TYPE,T.CPU_P1,T.CPU_P2,T.CPU_P3 ,T.MANDATORY,MENTS2 FROM DBA_RSRC_PLAN_DIRECTIVES T WHERE T.PLAN='SIMPLE_PLAN1';PLAN GROUP_OR_SUBPLANTYPE CPU_P1 CPU_P2 CPU_P3 MANDATORY COMMENTS-------------------- -------------------- -------------------- ------ ------ ------ ---------- --------------------------SIMPLE_PLAN1 MAILGRP CONSUMER_GROUP 0800 NO Level 2 Group 1SIMPLE_PLAN1 SYS_GROUP CONSUMER_GROUP10000 NO SYS Level 1SIMPLE_PLAN1 OTHER_GROUPS CONSUMER_GROUP 00 100 NO OTHER_GROUPS Level 3SIMPLE_PLAN1 MGRGRPCONSUMER_GROUP 0200 NO Level 2 Group 2SQL> COL ATTRIBUTE FOR A20SQL> COL VALUE FOR A20SQL> COL CONSUMER_GROUP FOR A20SQL> COL STATUS FOR A20SQL> SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;ATTRIBUTE VALUECONSUMER_GROUP STATUS-------------------- -------------------- -------------------- --------------------ORACLE_USERMAIL MAILGRPORACLE_USERMGR MGRGRPORACLE_USERSYS SYS_GROUPORACLE_USERSYSTEMSYS_GROUPSQL> COL CONSUMER_GROUP FOR A25SQL> COL CPU_METHOD FOR A20SQL> COL STATUS FOR A10SQL> COL MANDATORY FOR A10SQL> COL COMMENTS FOR A50SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;CONSUMER_GROUP CPU_METHOD COMMENTS STATUSMANDATORY------------------------- -------------------- -------------------------------------------------- ---------- ----------MAILGRP ROUND-ROBINLevel 2 Group 1 NOMGRGRPROUND-ROBINLevel 2 Group 2 NOOTHER_GROUPS ROUND-ROBINconsumer group for users not included in any group YESin the active top-planDEFAULT_CONSUMER_GROUP ROUND-ROBINconsumer group for users not assigned to any group YESSYS_GROUP ROUND-ROBINGroup of system sessionsYESLOW_GROUP ROUND-ROBINGroup of low priority sessions NOAUTO_TASK_CONSUMER_GROUP ROUND-ROBINSystem maintenance task consumer group NO已选择7行。SQL> --4.开启simple_plan1资源计划SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='SIMPLE_PLAN1';系统已更改。SQL> --访问该视图查看活动的资源计划SQL> SELECT * FROM V$RSRC_PLAN;ID NAME IS_TO---------- -------------------------------- -----66146 SIMPLE_PLAN1 TRUESQL> --这个时候INITIAL_RSRC_CONSUMER_GROUP显示了初始的组，但实际上并不会被应用，必须赋权才行SQL> SELECT T.USERNAME,T.INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS T2 WHERE T.USERNAME IN('MAIL','MGR');USERNAME INITIAL_RSRC_CONSUMER_GROUP------------------------------ ------------------------------MGR MGRGRPMAIL MAILGRPSQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T2 WHERE T.USERNAME IN('MAIL','MGR');SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP---------- ---------- ------------------------------ --------------------------------1335 MGR OTHER_GROUPS135 32 MGR OTHER_GROUPSSQL> --5.为指定用户赋于切换到指定组的权限，使该用户可以使用组SQL> BEGIN2 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('mail', 'mailgrp', TRUE);3 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('mgr', 'mgrgrp', TRUE);4 END;5 /PL/SQL 过程已成功完成。SQL> --6.在其它会话中，以MAIL用户登录，由以下输出可知，MAILGRP组已被应用。SQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T2 WHERE T.USERNAME IN('MAIL','MGR');SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP---------- ---------- ------------------------------ --------------------------------1335 MGR OTHER_GROUPS135 32 MGR OTHER_GROUPS1373 MAIL MAILGRPSQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T2 WHERE T.USERNAME IN('MAIL','MGR');SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP---------- ---------- ------------------------------ --------------------------------1335 MGR OTHER_GROUPS135 32 MGR OTHER_GROUPS1373 MAIL MAILGRP140 56 MGR MGRGRP
相关视图
参考：UsingtheDatabaseResourceManager/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776
