Viewing Database Resource Manager Information - Oracle 10g

The following table lists views that are associated with Database Resource Manager:

Viewing Database Resource Manager Information

You can use these views for viewing privileges, viewing plan schemas, or you can monitor them to gather information for tuning the Database Resource Manager. Some examples of their use follow.

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 can belong to the consumer groups market or sales, he has the ability to assign (grant) other users to the sales group but not the market group. Neither group is his initial consumer group.

SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

GRANTEE GRANTED_GROUP GRA INI

Viewing Plan Schema Information

This example shows using the DBA _RSRC _PLANS view to display all of the resource plans defined in the database. All of the plans displayed are active, meaning they are not staged in the pending area

SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANs;Viewing Plan Schema Information

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;

Viewing Plan Schema Information

Viewing the Currently Active Plans

This example sets mydb_plan, as created by the statements shown earlier in "Multilevel Schema Example" as the top level plan. The V$RSRC_PLAN view is queried to display the currently active plans.



All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics