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,STATUS, COMMENTS FROM DBA_RSRC_PLANS;

PLAN                        STATUS   COMMENTS
--------------------------- -------- ----------------------------------------
DSS_PLAN                             Example plan for DSS workloads that prio...
ETL_CRITICAL_PLAN                    Example plan for DSS workloads that prio...
MIXED_WORKLOAD_PLAN                  Example plan for a mixed workload that p...
ORA$AUTOTASK_SUB_PLAN                Default sub-plan for automated maintenan...
DEFAULT_MAINTENANCE_PLAN             Default plan for maintenance windows tha...
DEFAULT_PLAN                         Default, basic, pre-defined plan that pr...
INTERNAL_QUIESCE                     Plan for quiescing the database.  This p...
INTERNAL_PLAN                        Internally-used plan for disabling the r...
ORA$AUTOTASK_HIGH_SUB_PLAN           Default sub-plan for high-priority, auto...

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