/*
|| Oracle 10g Scheduler - Listing 3
||
|| Contains:
||
|| - Various DBMS_SCHEDULER examples
||
|| for demonstration of Oracle 10g Scheduler advanced features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| Scheduler and should be carefully proofread before executing it against
|| any existing Oracle database to insure that no potential damage can occur.
||
*/
-----
-- Listing 3.1: Creating Scheduler Job Classes
-----
BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS(
job_class_name => 'AccountingRpts'
,resource_consumer_group => NULL
,service => NULL
,logging_level => DBMS_SCHEDULER.LOGGING_FULL
,log_history => 60
,comments => 'Jobs responsible for Accounting Reports'
);
DBMS_SCHEDULER.CREATE_JOB_CLASS(
job_class_name => 'DBManagement'
,resource_consumer_group => NULL
,service => NULL
,logging_level => DBMS_SCHEDULER.LOGGING_RUNS
,log_history => 90
,comments => 'Database Management jobs'
);
END;
/
-----
-- Listing 3.2: Adding existing Jobs to Job Classes
-----
BEGIN
-- Add existing Schema Refresh tasks to DBManagement Job Class
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'HR_FRESHENSCHEMA'
,attribute => 'job_class'
,value => 'DBManagement'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'SH_FRESHENSCHEMA'
,attribute => 'job_class'
,value => 'DBManagement'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'SLSMGR_FRESHENSCHEMA'
,attribute => 'job_class'
,value => 'DBManagement'
);
END;
/
-----
-- Listing 3.3: Creating Scheduler Windows
-----
BEGIN
-- Create new Scheduler Windows
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'PEAK_AM'
,resource_plan => 'PEAKTIME'
,start_date => '10/04/2004 06:00:00'
,duration => INTERVAL '6' HOUR
,repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI'
,window_priority => 'HIGH'
,comments => 'Weekday Morning Processing'
);
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'PEAK_PM'
,resource_plan => 'PEAKTIME'
,start_date => '10/04/2004 12:00:00'
,duration => INTERVAL '6' HOUR
,repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI'
,window_priority => 'HIGH'
,comments => 'Weekday Afternoon Processing'
);
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'OFF_DAYS'
,resource_plan => 'OFFPEAK'
,start_date => '10/04/2004 17:30:00'
,duration => INTERVAL '12' HOUR
,repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI'
,window_priority => 'LOW'
,comments => 'Weekday Off-Peak Processing'
);
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'WKENDS'
,resource_plan => 'OFFPEAK'
,start_date => '10/09/2004 06:00:00'
,duration => INTERVAL '24' HOUR
,repeat_interval => 'FREQ=DAILY;BYDAY=SAT,SUN'
,window_priority => 'LOW'
,comments => 'Off-Peak Weekends'
);
END;
/
-----
-- Listing 3.4: Creating Scheduler Windows Using a Schedule
-----
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name => 'SPECIALCASE'
,schedule_name => 'FRESHENSCHEMAS'
,resource_plan => 'BATCH'
,duration => INTERVAL '1' WEEK
,comments => 'Special window for emergency schema refreshes'
);
END;
/
-----
-- Listing 3.5: Forcing Scheduler Windows to open and close
-----
-- Close a currently-open Window ...
BEGIN
DBMS_SCHEDULER.CLOSE_WINDOW(
window_name => 'OFF_DAYS'
);
END;
/
-- ... and force a currently-closed Window to open
BEGIN
DBMS_SCHEDULER.OPEN_WINDOW(
window_name => 'PEAK_PM'
,force -> TRUE
);
END;
/
-----
-- Listing 3.6: What Scheduler Windows have opened or closed recently?
-----
TTITLE 'Recent Window Status:'
COL oper_date FORMAT A20 HEADING 'Log Date'
COL window_name FORMAT A20 HEADING 'Window Name'
COL operation FORMAT A12 HEADING 'Open/|Closed'
SELECT
TO_CHAR(log_date, 'mm-dd-yyyy hh24:mi:ss') oper_date
,window_name
,operation
FROM dba_scheduler_window_log
WHERE operation IN ('OPEN', 'CLOSE')
ORDER BY log_date DESC
;
-----
-- Listing 3.7: Creating Scheduler Window Groups
-----
BEGIN
-- Create new Scheduler Window Groups
DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
group_name => 'PEAKTIME'
,window_list => 'PEAK_AM,PEAK_PM'
,comments => 'PeakTime Processing'
);
DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
group_name => 'OFFPEAK'
,window_list => 'OFF_DAYS,WKENDS'
,comments => 'PeakTime Processing'
);
END;
/
-----
-- Listing 3.8: Using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
-----
CREATE OR REPLACE PROCEDURE SYS.show_calendar_events(
InitialDate IN DATE
,ScheduleString IN VARCHAR2
,Iterations IN NUMBER)
AS
-- Local variables
dtz_begin TIMESTAMP;
dtz_next TIMESTAMP;
dtz_returned TIMESTAMP;
BEGIN
-- Convert incoming date to TIMESTAMP format
dtz_begin := TO_TIMESTAMP_TZ(InitialDate, 'mm/dd/yyyy hh24:mi:ss');
-- Set the returned date to the start date (for 1st iteration)
dtz_returned := dtz_begin;
FOR idx IN 1..Iterations
LOOP
-- Evaluate the calendar string
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
ScheduleString
,dtz_begin
,dtz_returned
,dtz_next
);
-- Print the output
DBMS_OUTPUT.PUT_LINE(
'Iteration #' || TO_CHAR(idx, '99999') ||
' will run on ' || dtz_next
);
-- Set up the next iteration
dtz_returned := dtz_next;
END LOOP;
END;
-----
-- Listing 3.9: Verifying a Scheduler Calendar String:
-- Starting on December 1, 2004, show the first 20 iterations of a scheduled
-- task that occur only on weekdays at 06:15 on those days
-----
SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(1000000);
BEGIN
SYS.show_calendar_events(
InitialDate => '12/01/2004'
,ScheduleString => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=15;BYDAY=MON,TUE,WED,THU,FRI'
,Iterations => 20
);
END;
/