创建一个资源计划

 创建一个资源计划

 

1.创建一个挂起区域：
SQL> execdbms_resource_manager.create_pending_area;
2.创建资源规划
SQL> exec dbms_resource_manager.create_plan(plan=>'test_plan',COMMENT=>'this is
testing plan') ;
3.创建用户组
SQL> execdbms_resource_manager.create_consumer_group( consumer_group=> 'demo',
comment => 'yes it is a demo' );
4.指定资源规划指令
SQL> execdbms_resource_manager.create_plan_directive( plan=>
'test_plan',group_or_subplan => 'demo',comment=> 'its a demo',MAX_EST_EXEC_TIME
=> 5 );
SQL> execdbms_resource_manager.create_plan_directive( plan=>
'test_plan',group_or_subplan => 'OTHER_GROUPS',comment => 'its a demo' );
5.验证变化
SQL> execdbms_resource_manager.validate_pending_area;
PL/SQL procedure successfully completed.
6.提交
SQL> execdbms_resource_manager.submit_pending_area;
PL/SQL procedure successfully completed.
7.创建一个用户
SQL> drop user user1 cascade;
SQL> create user user1 identified by abc123;
SQL> grant connect, resource to user1;
8.授予切换权限,其实就一并将资源规则分配给这个用户

SQL> execdbms_resource_manager_privs.grant_switch_consumer_group ('user1',
'demo', TRUE );

PL/SQL procedure successfully completed.

--其实dbms_resource_manager_privs.grant_switch_consumer_group就是将切换权限授予用户user1，并让用户会话有权限可以通过过程DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('demo', old_group,FALSE);(--下边例子会讲)切换到用户组demo上，这意味着在demo用户组上的资源规则将用于用户user1。

 

关于dbms_resource_manager_privs--插讲dbms_resource_manager_privs

文档地址：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_resmpr.htm#CFAEFBIB

 

Summary of DBMS_RESOURCE_MANAGER_PRIVS Subprograms

 

Table 86-1 DBMS_RESOURCE_MANAGER_PRIVS Package Subprograms

Subprogram	Description
GRANT_SWITCH_CONSUMER_GROUP Procedure	Grants the privilege to switch to resource consumer groups
GRANT_SYSTEM_PRIVILEGE Procedure	Performs a grant of a system privilege
REVOKE_SWITCH_CONSUMER_GROUP Procedure	Revokes the privilege to switch to resource consumer groups.
REVOKE_SYSTEM_PRIVILEGE Procedure	Performs a revoke of a system privilege

---

 

GRANT_SWITCH_CONSUMER_GROUP Procedure

 

This procedure grants the privilege to switch to a resourceconsumer group.

Syntax

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   grantee_name   IN VARCHAR2, 
   consumer_group IN VARCHAR2, 
   grant_option   IN BOOLEAN);

Parameters

Table 86-2 GRANT_SWITCH_CONSUMER_GROUPProcedure Parameters

Parameter	Description
grantee_name	Name of the user or role to whom privilege is to be granted.
consumer_group	Name of consumer group.
grant_option	TRUE if grantee should be allowed to grant access,FALSE otherwise.

Usage Notes

If you grant permission to switch to a particular consumer groupto a user, then that user can immediately switch their currentconsumer group to the new consumer group.

If you grant permission to switch to a particular consumer groupto a role, then any users who have been granted that role and haveenabled that role can immediately switch their current consumergroup to the new consumer group.

If you grant permission to switch to a particular consumer groupto PUBLIC, then any user can switch to that consumergroup.

If the grant_option parameter is TRUE,then users granted switch privilege for the consumer group may alsogrant switch privileges for that consumer group to others.

In order to set the initial consumer group of a user, you mustgrant the switch privilege for that group to the user.

 

 

Examples

BEGIN 
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ( 
 'scott', 'mail_maintenance_group', true); 
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); 
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping( 
  dbms_resource_manager.oracle_user, 'scott','mail_maintenance_group'); 
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); 
END; 
/ 

 

 

GRANT_SYSTEM_PRIVILEGE Procedure

 

This procedure performs a grant of a system privilege to a useror role.

Syntax

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
   grantee_name   IN VARCHAR2, 
   privilege_name  IN VARCHAR2 DEFAULT 'ADMINISTER_RESOURCE_MANAGER',             
   admin_option   IN BOOLEAN);

Parameters

Table 86-3 GRANT_SYSTEM_PRIVILEGE ProcedureParameters

Parameter	Description
grantee_name	Name of the user or role to whom privilege is to be granted.
privilege_name	Name of the privilege to be granted.
admin_option	TRUE if the grant is withadmin_option, FALSE otherwise.

Usage Notes

Currently, Oracle provides only one system privilege for theResource Manager: ADMINISTER_RESOURCE_MANAGER.Database administrators have this system privilege with theADMIN option. The grantee and the revokee can eitherbe a user or a role. Users that have been granted the systemprivilege with the ADMIN option can also grant thisprivilege to others.

Examples

The following call grants this privilege to a user calledscott without the ADMIN option:

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
 grantee_name => 'scott',
 privilege_name => 'ADMINISTER_RESOURCE_MANAGER',
 admin_option => FALSE);
END;
/

---DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE 此过程授予用户SCOTT拥有管理RESOURCE_MANAGER的系统权限

 

9.SQL> execdbms_resource_manager.set_initial_consumer_group ( 'user1', 'demo');
PL/SQL procedure successfully completed.

 

--若此处不执行这条语句的话，那么用户user1在登录的时候会将DEFAULT_CONSUMER_GROUP作为默认的初始化用户组。因此此会话若想切换到用户组demo上的话，那么需要执行一个包的过程：DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('demo',old_group,FALSE);这会切换到用户组demo上，前提是必须通过过程dbms_resource_manager_privs.grant_switch_consumer_group（上有介绍）给该用户授予切换权限。

 

下是文档地址（关于包DBMS_SESSION）：http://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm#sthref3411

 

Using the DBMS_SESSION Package to Switch ConsumerGroup

 

If granted the switch privilege, users can switch theircurrent consumer group using theSWITCH_CURRENT_CONSUMER_GROUP procedure in theDBMS_SESSION package.

This procedure enables users to switch to a consumer group forwhich they have the switch privilege. If the caller is anotherprocedure, then this procedure enables users to switch to aconsumer group for which the owner of that procedure has switchprivileges.

The parameters for this procedure are:

Parameter	Description
NEW_CONSUMER_GROUP	The consumer group towhich the user is switching.
OLD_CONSUMER_GROUP	An output parameter.Stores the name of the consumer group from which the user switched.Can be used to switch back later.
INITIAL_GROUP_ON_ERROR	Controls behavior if aswitching error occurs. If TRUE, in the event of an error, the user isswitched to the initial consumer group. If FALSE, raise an error.

The following example illustrates switching to a new consumergroup. By printing the value of the output parameterold_group, we illustrate how the old consumer groupname has been saved.

SET serveroutput on
DECLARE
    old_group varchar2(30);
BEGIN
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
END;

The following line is output:

OLD GROUP = DEFAULT_CONSUMER_GROUP

The DBMS_SESSION package can be used from within aPL/SQL application, thus allowing the application to changeconsumer groups, or effectively priority, dynamically.

Note that the Database Resource Manager considers a switch tohave taken place even if theSWITCH_CURRENT_CONSUMER_GROUP procedure is called toswitch the session to the consumer group that it is already in.

 

 

 

10. 启动数据库资源管理器
SQL> alter system set resource_manager_plan =test_plan scope = memory;
System altered.
10.用户登录检验设置
SQL> conn user1/abc123;
SQL> create table t as select * fromall_objects;
SQL> analyze table t compute statistics;
SQL> select count(*) from t;
COUNT(*)
----------
40138
SQL> select count(*) fromt,t;--此语句执行笛卡尔积查询，故数据会非常庞大，时间会很长。
select count(*) from t,t
ERROR at line 1:
ORA-07455: estimated execution time (59781 secs), exceeds limit (5secs)
可以看到,已经被限制了.