Viewing Database Resource Manager Configuration and Status

Viewing Consumer Groups Granted to Users or Roles

The DBA_RSRC_CONSUMER_GROUP_PRIVS view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched. For example, in the view shown below, user SCOTT always starts in the SALES consumer group, can switch to the MARKETING group through a specific grant, and can switch to the DEFAULT_CONSUMER_GROUP and LOW_GROUP groups because they are granted to PUBLIC. SCOTT also has the ability to grant the SALES group but not the MARKETING group to other users.

SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

GRANTEE            GRANTED_GROUP                  GRANT_OPTION INITIAL_GROUP
------------------ ------------------------------ ------------ -------------
PUBLIC             DEFAULT_CONSUMER_GROUP         YES          YES
PUBLIC             LOW_GROUP                      NO           NO
SCOTT              MARKETING                      NO           NO
SCOTT              SALES                          YES          YES
SYSTEM             SYS_GROUP                      NO           YES

SCOTT was granted the ability to switch to these groups using the DBMS_RESOURCE_MANAGER_PRIVS package.

Viewing Plan Information

This example uses the DBA_RSRC_PLANS view to display all of the resource plans defined in the database. All plans have a NULL status, meaning that they are not in the pending area.

SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS;

PLAN                        COMMENTS                                     STATUS
--------------------------  -------------------------------------------  ------
MIXED_WORKLOAD_PLAN         Example plan for a mixed workload that prio
ORA$AUTOTASK_SUB_PLAN       Default sub-plan for automated maintenance 
ORA$AUTOTASK_HIGH_SUB_PLAN  Default sub-plan for high-priority, automat
ERP_PLAN                    Resource plan/method for ERP Database      
DEFAULT_PLAN                Default, basic, pre-defined plan that prior
INTERNAL_QUIESCE            Plan for quiescing the database.  This plan
INTERNAL_PLAN               Internally-used plan for disabling the reso
DEFAULT_MAINTENANCE_PLAN    Default plan for maintenance windows that p

Viewing Current Consumer Groups for Sessions

You can use the V$SESSION view to display the consumer groups that are currently assigned to sessions.

SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;

SID    SERIAL#  USERNAME                  RESOURCE_CONSUMER_GROUP
-----  -------  ------------------------  --------------------------------
.
.
.
   11       136 SYS                       SYS_GROUP
   13     16570 SCOTT                     SALES

Viewing the Currently Active Plans

This example sets mydb_plan, as created by the example shown earlier in "Multilevel Plan Example", as the top level plan. It then queries the V$RSRC_PLAN view to display the currently active plans. The view displays the current top level plan and all of its descendent subplans.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;

System altered.

SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;

NAME             IS_TOP_PLAN
----------------------------
MYDB_PLAN        TRUE
MAILDB_PLAN      FALSE
BUGDB_PLAN       FALSE