Oracle 11g R1中资源管理器增强

核心提示： BEGIN-- Assign users to consumer groupsDBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(grantee_name => 'test',consumer_group =

BEGIN

-- Assign users to consumer groups

DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(

grantee_name => 'test',

consumer_group => 'interactive_group',

grant_option => FALSE);

DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(

grantee_name => 'test',

consumer_group => 'batch_group',

grant_option => FALSE);

DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group');

END;

/

SELECT initial_rsrc_consumer_group FROM dba_users WHERE username = 'TEST';

INITIAL_RSRC_CONSUMER_GROUP

------------------------------

INTERACTIVE_GROUP

1 row selected.

SQL>

以TEST用户连接到数据库，查询v$session视图正在工作的资源消耗组。　

CONN test/test

SELECT resource_consumer_group

FROM v$session

WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

RESOURCE_CONSUMER_GROUP

--------------------------------

INTERACTIVE_GROUP

1 row selected.

SQL>

如果我们创建一个长时间的调用，可以看到资源消耗组的切换。　

CONN test/test

SET SERVEROUTPUT ON

DECLARE

l_cg v$session.resource_consumer_group%TYPE;

BEGIN

SELECT resource_consumer_group

INTO l_cg

FROM v$session

WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);

-- Sleep to cause switch based on time.

DBMS_LOCK.sleep(65);

SELECT resource_consumer_group

INTO l_cg

FROM v$session

WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);

END;

/

Current Consumer Group: INTERACTIVE_GROUP

Current Consumer Group: BATCH_GROUP

PL/SQL procedure successfully completed.

SQL>

正如我们所期望的，调用最初分配的是INTERACTIVE_GROUP资源组，当调用超过60秒时就切换到BATCH_GROUP组了。