Creating a resource manager plan directive in Oracle

 Creating a resource manager plan directive in Oracle

In Oracle, Resource plan directives specify how resources are allocated to resource consumer groups or subplans. Each directive can specify several different methods for allocating resources to its consumer group or subplan.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:03:48 2023

Version 21.3.0.0.0

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

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> alter session set container=PDB1

Session altered.

SQL> BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

   PLAN             => 'DAYTIME',

   GROUP_OR_SUBPLAN => 'OLTP',

   COMMENT          => 'OLTP group',

   MGMT_P1          => 75,

   SWITCH_GROUP     => 'LOW_GROUP',

   SWITCH_TIME      => 5);

END;

/  2    3    4    5    6    7    8    9   10

BEGIN

*

ERROR at line 1:

ORA-29371: pending area is not active

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 2152

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 847

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1867

ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 345

ORA-06512: at line 2

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA()

PL/SQL procedure successfully completed.

SQL> ed

Wrote file afiedt.buf

  1  BEGIN

  2    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

  3     PLAN             => 'DAYTIME',

  4     GROUP_OR_SUBPLAN => 'OLTP',

  5     COMMENT          => 'OLTP group',

  6     MGMT_P1          => 75,

  7     SWITCH_GROUP     => 'LOW_GROUP',

  8     SWITCH_TIME      => 5);

  9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL>  exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()

PL/SQL procedure successfully completed.