| Lesson 7 | Use Database Resource Manager in Oracle |
| Objective | Use Database Resource Manager to limit resource usage. |
Use Database Resource Manager to limit resource usage
The previous lesson described how to design a resource management plan. This lesson shows you how to implement the plan. Implementing a resource management plan means loading information into the database using several procedures stored in the DBMS_RESOURCE_MANAGER package.
This package is installed with all Oracle Enterprise Edition databases. Look at the following series of images for syntax and examples of each step. The code shown here can be executed in SQL*Plus.
Oracle SQL*Plus File Edit Search Options Help 1 SQL> BEGIN 2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; 3 END; 4 / PL/SQL procedure successfully completed. SQL>
Relevant Features:
- SQL*Plus Environment: The screenshot shows the SQL*Plus interface, a tool used for executing SQL and PL/SQL commands interactively or from a script. The menu bar contains typical options like File, Edit, Search, and Help, indicating the application is likely running on a Windows platform.
- PL/SQL Block Execution: The text block is a PL/SQL anonymous block that executes a single procedure: `DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA`.
- Procedure Purpose: The `CREATE_PENDING_AREA` procedure is part of the Oracle Database Resource Manager (DBRM). This procedure initializes a pending area where changes to the resource manager configuration can be staged before being committed. The use of this procedure suggests that the user is preparing to make configuration changes related to resource management.
- Execution Feedback: The message "PL/SQL procedure successfully completed." indicates that the procedure executed without errors, signifying the pending area was created successfully.
- User Guidance: At the bottom of the screenshot, there's a text box providing guidance on the procedure's purpose: "When you start working with the Database Resource Manager, your first task is to tell the service that you are going to make a change. Do this by executing the CREATE_PENDING_AREA procedure as shown here."
This screenshot is typically used for educational or documentation purposes to illustrate the step-by-step process of working with Oracle's DBMS Resource Manager, especially during initial configuration stages.
Oracle SQL*Plus File Edit Search Options Help 1 SQL> BEGIN 2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; 3 END; 4 / PL/SQL procedure successfully completed. SQL> 1 SQL> BEGIN 2 DBMS_RESOURCE_MANAGER.CREATE_PLAN ( 3 plan => 'ESALES_PLAN', 4 comment => 'New plan.'); 5 END; 6 / PL/SQL procedure successfully completed. SQL>Here is the code for creating a new plan. In this example, the plan name is ESALES_PLAN (we are using the example from the previous lesson.)
Relevant Features:
- SQL*Plus Environment: Like the previous screenshot, this one is also set in the SQL*Plus interface with the usual menu options indicating a Windows environment.
- PL/SQL Block Execution:
- The first block executes the `CREATE_PENDING_AREA` procedure again, which, as mentioned before, sets up a pending area for staging configuration changes.
- The second block creates a new resource plan using `DBMS_RESOURCE_MANAGER.CREATE_PLAN`. The plan is named `ESALES_PLAN`, and it includes a comment describing the plan as "New plan." This suggests configuration for a specific purpose or application (in this case, "ESALES").
- Execution Feedback: Both blocks show a message indicating "PL/SQL procedure successfully completed," confirming the successful execution of the commands without errors.
- Contextual Information: The instructional text at the bottom provides context for the second block, noting that the code snippet is for creating a new resource plan named `ESALES_PLAN` and ties it to the previous lesson, likely from a tutorial or educational course.
This image isuseful for educational purposes or as a guide for database administrators on how to configure and manage resource plans using the Oracle Database Resource Manager. The example demonstrates how to sequentially set up a pending area and then create a resource management plan, illustrating typical steps involved in configuring database resource management settings.
Using these commands is easy. The most important part of using the Database Resource Manager is to define a plan or set of plans appropriate for your system. Look in the Oracle Administrator's Guide for examples of a variety of system scenarios and plans.
DB Resource Manager - Exercise
Click the Exercise link below to practice creating a plan directive, defining a consumer group, and assigning a user to the group.
The next lesson wraps up this module.
DB Resource Manager - Exercise
The next lesson wraps up this module.
DB Resource Manager - Exercise