Table Space Management   «Prev 

Oracle Resource Management Plan

Example of Resource Management Plan

Example of a Resource Management Plan
Example of a Resource Management Plan
  1. ESALES_PLAN
  2. TOP_PRIORITY, MEDIUM_PRIORITY
  3. WEBUSER, MANAGER

Location 1 Plan name
Location 2 Plan directives
Location 3 Consumer groups

Routine Database Maintenance Tasks

Oracle Database is preconfigured to perform some routine database maintenance tasks so that you can run them at times when the system load is expected to be light. You can specify for such a time period a resource plan that controls the resource consumption of those maintenance tasks.

Maintenance Windows

Oracle Scheduler enables you to create time windows during which jobs are automatically run. A typical Scheduler window defines a start time, a duration, and optionally a resource plan to activate. A Scheduler job can then name a window as its schedule. (When the window "opens," the job begins to run.) In addition, windows can be combined into window groups, and if a job names a window group as its schedule instead of naming a window, the job runs whenever any of the windows in the window group opens.
Two Scheduler windows are predefined upon installation of Oracle Database:
  1. WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
  2. WEEKEND_WINDOW covers whole days Saturday and Sunday.

Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection, for space management, and for some other internal system maintenance jobs.
You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure. For example, the following script moves the WEEKNIGHT_WINDOW to midnight to 8 a.m. every weekday morning: 
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
'WEEKNIGHT_WINDOW',
'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;
byhour=0;byminute=0;bysecond=0');

Window Duration

Note that the duration of the window was already eight hours, so the script did not need to change it.
You can also use the SET_ATTRIBUTE procedure to adjust any other property of a window. For example, the following script sets resource plan
DEFAULT_MAINTENANCE_PLAN for the WEEKNIGHT_WINDOW: 
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE (
'WEEKNIGHT_WINDOW',
'resource_plan',
'DEFAULT_MAINTENANCE_PLAN');

In this case, if you have already enabled a different resource plan, Oracle Database will make the DEFAULT_MAINTENANCE_PLAN active when the WEEKNIGHT_WINDOW opens, and will reactivate the original resource plan when the WEEKNIGHT_WINDOW closes.