ORM举例

    REM 创建测试用户

    CREATE USER OA_USER IDENTIFIED BY iv1234
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    ACCOUNT UNLOCK;

    CREATE USER OA_ADMIN IDENTIFIED BY iv1234
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP    ACCOUNT UNLOCK;

    GRANT CONNECT TO OA_USER;
    GRANT CONNECT TO OA_ADMIN;

    –创建相关的组

    BEGIN
  --清楚ORM临时区域
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  --创建新的ORM临时区域
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   CONSUMER_GROUP  =>'OA_USER_ONLINE',
   COMMENT         =>'创建OA系统的资源管理组');

 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   CONSUMER_GROUP  =>'OA_USER_ADMIN',
   COMMENT         =>'OA 系统的后台服务资源组');

  --创建PLAN

  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN             =>'OA_ORM',
   COMMENT          =>'OA 数据库资源计划');

    --创建PLAN DIRECTIVES

    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN               =>'OA_ORM',      GROUP_OR_SUBPLAN   =>'OA_USER_ONLINE',      COMMENT            =>'oa在线用户资源控制计划',
    CPU_P1             =>70,
    CPU_P2             =>0,
    ACTIVE_SESS_POOL_P1 =>3000,
    PARALLEL_DEGREE_LIMIT_P1 =>2,
    MAX_EST_EXEC_TIME =>900);

    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN               =>'OA_ORM',
    GROUP_OR_SUBPLAN   =>'OA_USER_ADMIN',
    COMMENT            =>'oa在线用户后台服务资源控制计划',
    CPU_P1             =>10,
    CPU_P2             =>0,
    ACTIVE_SESS_POOL_P1 =>5,
    PARALLEL_DEGREE_LIMIT_P1 =>2,
    MAX_EST_EXEC_TIME =>900);

    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN               =>'OA_ORM',      GROUP_OR_SUBPLAN   =>'OTHER_GROUPS',      COMMENT            =>'其他应用的资源计划',
    CPU_P1             =>0,
    CPU_P2             =>100,
    PARALLEL_DEGREE_LIMIT_P1 =>2);

    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

    END;
    /

    –初始化OA_USER、OA_ADMIN的资源组

    BEGIN

    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME =>'OA_USER',
    CONSUMER_GROUP=>'OA_USER_ONLINE',
    GRANT_OPTION=>FALSE);

    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME =>'OA_ADMIN',
    CONSUMER_GROUP=>'OA_USER_ADMIN',
    GRANT_OPTION=>FALSE);

    DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
    USER =>'OA_USER',
    CONSUMER_GROUP=>'OA_USER_ONLINE');
    DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
    USER =>'OA_ADMIN',
    CONSUMER_GROUP=>'OA_USER_ADMIN');

    --允许OA_ADMIN可以替换到GROUP OA_USER_ONLINE
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    GRANTEE_NAME =>'OA_ADMIN',
    CONSUMER_GROUP=>'OA_USER_ADMIN',
    GRANT_OPTION=>FALSE);
    END;
    /

    –查询GROUP的切换

    select username,initial_rsrc_consumer_group from dba_users WHERE username IN('OA_USER','OA_ADMIN');

    USERNAME
    ------------------------------------------------------------
    INITIAL_RSRC_CONSUMER_GROUP
    ------------------------------------------------------------
    OA_ADMIN
    OA_USER_ADMIN

    OA_USER
    OA_USER_ONLINE
    
    –在数据库中启用OA_ORM plans

    ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='OA_ORM' scope=BOTH;

    System altered.

    –看看session的登录信息

    select username,resource_consumer_group from v$session where username='OA_USER';

    USERNAME
    ------------------------------------------------------------
    RESOURCE_CONSUMER_GROUP
    ----------------------------------------------------------------
    OA_USER
    OA_USER_ONLINE

    select username,resource_consumer_group from v$session where username='OA_ADMIN';

    USERNAME
    ------------------------------------------------------------
    RESOURCE_CONSUMER_GROUP
    ----------------------------------------------------------------
    OA_ADMIN
    OA_USER_ADMIN