Resource Manager限制用户资源使用

      数据库新建了用户供外围系统使用，领导要求限制用户执行语句时长。用resource manager来实现。下面是实现过程

[oracle@t5b01]:/export/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 3 16:25:04 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     256
resource_manager_plan                string

SQL> begin
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager_privs.grant_system_privilege(grantee_name=>'JSNYYW',admin_option=>true);
  5     dbms_resource_manager.submit_pending_area();
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2   dbms_resource_manager.clear_pending_area();
  3   dbms_resource_manager.create_pending_area();
  4   dbms_resource_manager.create_consumer_group(consumer_group =>'LOW_RESOURCE_GROUP',comment=>'Limite Resource Use');
  5   dbms_resource_manager.submit_pending_area();
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'JSNYYW','LOW_RESOURCE_GROUP');
  5     dbms_resource_manager.submit_pending_area();
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'JSNYYW',consumer_group => 'LOW_RESOURCE_GROUP',grant_option => false);
  5     dbms_resource_manager.submit_pending_area();
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager.set_initial_consumer_group(user=>'JSNYYW',consumer_group=>'LOW_RESOURCE_GROUP');
  5     dbms_resource_manager.submit_pending_area();
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager.create_plan('JCSJ_PLAN','Plan for JCSJ');
  5     dbms_resource_manager.create_plan_directive(plan=> 'JCSJ_PLAN',group_or_subplan =>'LOW_RESOURCE_GROUP',comment=>'FOR SWITCH TIME',switch_group=>'CANCEL_SQL',switch_time =>7200,max_idle_time=>1800);
  6     dbms_resource_manager.create_plan_directive(plan=> 'JCSJ_PLAN',group_or_subplan =>'OTHER_GROUPS',comment=>'FOR OTHER');
  7     dbms_resource_manager.submit_pending_area();
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     256
resource_manager_plan                string

SQL> alter system set resource_manager_plan='JCSJ_PLAN' scope=memory sid='*';

System altered.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     256
resource_manager_plan                string      JCSJ_PLAN

 

SQL> select inst_id,username,RESOURCE_CONSUMER_GROUP from gv$session where RESOURCE_CONSUMER_GROUP='LOW_RESOURCE_GROUP';

   INST_ID USERNAME                       RESOURCE_CONSUMER_GROUP
---------- ------------------------------ --------------------------------
         1 JSNYYW                         LOW_RESOURCE_GROUP
         2 JSNYYW                         LOW_RESOURCE_GROUP
         2 JSNYYW                         LOW_RESOURCE_GROUP
         2 JSNYYW                         LOW_RESOURCE_GROUP

超过限制的会话将会收到下面消息：

ORA-00040: active time limit exceeded - call aborted

 

如果发生异常需要取消PLAN，sqlplus中执行
alter system set resource_manager_plan='' sid='*';