ORACLE RESOURCE MANAGER（资源管理器）
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition>
PL/SQL>
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> --1.创建简单资源计划
SQL> BEGIN
2DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
3 CONSUMER_GROUP1 => 'mailgrp', GROUP1_CPU => 80,
4 CONSUMER_GROUP2 => 'mgrgrp', GROUP2_CPU => 20);
5END;
6/
PL/SQL 过程已成功完成。
SQL> --2.为组和用户指定映射规则
SQL> BEGIN
2DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
4 (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'mail', 'mailgrp');
5DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
6 (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'mgr', 'mgrgrp');
7END;
8/
PL/SQL 过程已成功完成。
SQL> --3.提交变更
SQL> BEGIN
2DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
3END;
4/
PL/SQL 过程已成功完成。
SQL> set linesize 200
SQL> 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> SELECTT.PLAN,T.CPU_METHOD,T.STATUS,T.MANDATORY,T.COMMENTSFROM DBA_RSRC_PLANS T;
PLAN CPU_METHOD STATUS MANDATORYCOMMENTS
-------------------- -------------------- ---------- ---------- --------------------------------------------------
SIMPLE_PLAN1 EMPHASIS NO Simple plan
INTERNAL_PLAN EMPHASIS YES Default Plan
INTERNAL_QUIESCE EMPHASIS YES Plan to internally quiesce system
SYSTEM_PLAN EMPHASIS NO Plan to give system sessions priority
SQL> COL PLAN FOR A20;
SQL> COL GROUP_OR_SUBPLAN FOR A20;
SQL> COL TYPE FOR A20;
SQL> COL CPU_P1 FOR 99999
SQL> COL CPU_P2 FOR 99999
SQL> COL CPU_P3 FOR 99999
SQL> 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,T.COMMENTS
2FROM DBA_RSRC_PLAN_DIRECTIVEST WHERE T.PLAN='SIMPLE_PLAN1';
PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3 MANDATORYCOMMENTS
-------------------- -------------------- -------------------- ------ ------ ------ ---------- --------------------------
SIMPLE_PLAN1 MAILGRP CONSUMER_GROUP 0 80 0 NO Level 2 Group 1
SIMPLE_PLAN1 SYS_GROUP CONSUMER_GROUP 100 0 0 NO SYS Level 1
SIMPLE_PLAN1 OTHER_GROUPS CONSUMER_GROUP 0 0 100 NO OTHER_GROUPS Level 3
SIMPLE_PLAN1 MGRGRP CONSUMER_GROUP 0 20 0 NO Level 2 Group 2
SQL> COL ATTRIBUTE FOR A20
SQL> COL VALUE FOR A20
SQL> COL CONSUMER_GROUP FOR A20
SQL> COL STATUS FOR A20
SQL> SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;
ATTRIBUTE VALUE CONSUMER_GROUP STATUS
-------------------- -------------------- -------------------- --------------------
ORACLE_USER MAIL MAILGRP
ORACLE_USER MGR MGRGRP
ORACLE_USER SYS SYS_GROUP
ORACLE_USER SYSTEM SYS_GROUP
SQL> COL CONSUMER_GROUP FOR A25
SQL> COL CPU_METHOD FOR A20
SQL> COL STATUS FOR A10
SQL> COL MANDATORY FOR A10
SQL> COL COMMENTS FOR A50
SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;
CONSUMER_GROUP CPU_METHOD COMMENTS STATUS MANDATORY
------------------------- -------------------- -------------------------------------------------- ---------- ----------
MAILGRP ROUND-ROBIN Level 2 Group 1 NO
MGRGRP ROUND-ROBIN Level 2 Group 2 NO
OTHER_GROUPS ROUND-ROBIN consumer group for users not included in any group YES
in the active top-plan
DEFAULT_CONSUMER_GROUP ROUND-ROBIN consumer group for users not assigned to any group YES
SYS_GROUP ROUND-ROBIN Group of system sessions YES
LOW_GROUP ROUND-ROBIN Group of low priority sessions NO
AUTO_TASK_CONSUMER_GROUPROUND-ROBIN System maintenance task consumer group NO
已选择7行。
SQL> --4.开启simple_plan1资源计划
SQL>>
系统已更改。
SQL> --访问该视图查看活动的资源计划
SQL> SELECT * FROM V$RSRC_PLAN;
ID NAME IS_TO
---------- -------------------------------- -----
66146 SIMPLE_PLAN1 TRUE
SQL> --这个时候INITIAL_RSRC_CONSUMER_GROUP显示了初始的组，但实际上并不会被应用，必须赋权才行
SQL> SELECT T.USERNAME,T.INITIAL_RSRC_CONSUMER_GROUPFROM DBA_USERS T
2WHERE T.USERNAME IN('MAIL','MGR');
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
MGR MGRGRP
MAIL MAILGRP
SQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T
2WHERE T.USERNAMEIN('MAIL','MGR');
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
133 5 MGR OTHER_GROUPS
135 32 MGR OTHER_GROUPS
SQL> --5.为指定用户赋于切换到指定组的权限，使该用户可以使用组
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('mail', 'mailgrp', TRUE);
3 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('mgr', 'mgrgrp', TRUE);
4END;
5/
PL/SQL 过程已成功完成。
SQL> --6.在其它会话中，以MAIL用户登录，由以下输出可知，MAILGRP组已被应用。
SQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T
2WHERE T.USERNAMEIN('MAIL','MGR');
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
133 5 MGR OTHER_GROUPS
135 32 MGR OTHER_GROUPS
137 3 MAIL MAILGRP
SQL> SELECT T.SID,T.SERIAL#,T.USERNAME,T.RESOURCE_CONSUMER_GROUP FROM V$SESSION T
2WHERE T.USERNAMEIN('MAIL','MGR');
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
133 5 MGR OTHER_GROUPS
135 32 MGR OTHER_GROUPS
137 3 MAIL MAILGRP
140 56 MGR MGRGRP
页:
[1]