/*
|| Oracle 10gR2 Scheduler Enhancements Listing 1
||
|| Demonstrates new Oracle 10gR2 Scheduler features, including:
|| - Examples of new Calendaring Syntax options
|| - Examples of combining Schedule objects to obtain new Schedules
|| - Examples of event-driven job scheduling
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Scheduling features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/
/*
|| Listing 1.1: Create Schedule objects illustrating new BYDATE directive
*/
-----
-- Create a schedule of all standard holidays for 2006 - 2010
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'StandardHolidays'
,start_date => TO_DATE('01/01/2006 00:00:00','mm/dd/yyyy hh24:mi:ss')
,end_date => TO_DATE('12/31/2010 23:59:59','mm/dd/yyyy hh24:mi:ss')
,repeat_interval =>
'FREQ=DAILY;
BYDATE=20060101,20060116,20060529,20060704,20060904
,20061123,20061124,20061224,20061225,20061231
,20070101,20070115,20070528,20070704,20070903
,20071122,20071123,20071224,20071225,20071231
,20080101,20080121,20080526,20080704,20080901
,20081127,20081128,20081224,20081225,20081231
,20090101,20090119,20090525,20090703,20090907
,20091126,20091127,20091224,20091225,20091231
,20100101,20100118,20100531,20100705,20100906
,20101125,20101126,20101223,20101224,20101231'
,comments => 'Standard Holidays, 2006 - 2010'
);
END;
/
/*
|| Listing 1.2: Using SPAN and OFFSET with BYDATE
*/
-----
-- Create a schedule that runs daily starting on the fifth day
-- of each calendar month and runs for the next twenty days
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'FifthDayCycle'
,repeat_interval =>
'FREQ=DAILY;
BYDATE=0105+SPAN:20D,0205+SPAN:20D,0305+SPAN:20D
,0405+SPAN:20D,0505+SPAN:20D,0605+SPAN:20D
,0705+SPAN:20D,0805+SPAN:20D,0905+SPAN:20D
,1005+SPAN:20D,1105+SPAN:20D,1205+SPAN:20D'
,comments => 'Standard 20-Day Billing Cycle'
);
END;
/
-----
-- Create a special schedule for late February and early March
-- that always starts exactly twenty days before March 15th
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'March15thBilling'
,repeat_interval =>
'FREQ=YEARLY;
BYDATE=0315-OFFSET:20D'
,comments => 'March 15th Special Billing Cycle'
);
END;
/
/*
|| Listing 1.3: Create Schedule objects by combining other Schedules
*/
-----
-- Create a "Thursdays only" batch processing schedule, and
-- combine it with the Standard Holidays schedule
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'ThursdaysOnlyBatchProcessing'
,start_date => TO_DATE('01/01/2006 00:00:00','mm/dd/yyyy hh24:mi:ss')
,repeat_interval =>
'FREQ=DAILY;
BYDAY=THU;
EXCLUDE=StandardHolidays'
,comments => 'Thursdays Only Batch Processing Schedule'
);
END;
/
-----
-- Create a special end-of-month schedule that starts on the
-- third-to-last day of each month through the last day
-- of each month for the last three months of 2006.
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SpecialEndOfMonth'
,start_date => TO_DATE('10/01/2006 00:00:00','mm/dd/yyyy hh24:mi:ss')
,end_date => TO_DATE('12/31/2006 23:59:59','mm/dd/yyyy hh24:mi:ss')
,repeat_interval =>
'FREQ=MONTHLY;
BYMONTHDAY=-3;
EXCLUDE=StandardHolidays'
,comments => '2006 Special End-Of-Month Processing Schedule'
);
END;
/
/*
|| Listing 1.4: Using BYSETPOS, PERIOD, and BYPERIOD
*/
-----
-- Create a special end-of-month schedule that only runs on the second-
-- to-last occurrence of a Tuesday, Thursday, or Saturday of every month
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SpecialClosingPeriod'
,repeat_interval =>
'FREQ=MONTHLY;
BYDAY=TUE,THU,SAT;
BYSETPOS=-2;
EXCLUDE=StandardHolidays'
,comments => 'Special Closing Period (Second-to-last T/Th/Sa)'
);
END;
/
-----
-- Create a standard end-of-month schedule
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'StandardEndOfMonth'
,repeat_interval =>
'FREQ=YEARLY;
BYMONTHDAY=-1;
PERIODS=12;
EXCLUDE=StandardHolidays'
,comments => 'Standard End-Of-Month Schedule (excludes Standard Holidays)'
);
END;
/
-----
-- Create a special quarterly schedule for selected months by
-- selecting the 3rd, 6th, 9th and 12th items in the user-defined
-- schedule already created
-----
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'PeriodicalEndOfMonth'
,repeat_interval =>
'FREQ=StandardEndOfMonth;
BYPERIOD=3,6,9,12'
,comments => 'Periodic End-Of-Month Schedule'
);
END;
/
/*
|| Listing 1.5: Evaluate the results of combined schedules
*/
-----
-- Create a stored procedure utilizing 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;
DBMS_OUTPUT.PUT_LINE('>>>');
DBMS_OUTPUT.PUT_LINE(
'Next ' || Iterations ||
' iterations of Schedule ' || ScheduleString ||
' as of ' || InitialDate);
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;
/
-----
-- Show selected iterations of various Schedules
-----
SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(1000000);
BEGIN
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('12/31/2005','mm/dd/yyyy')
,ScheduleString => 'StandardHolidays'
,Iterations => 50
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('01/01/2006','mm/dd/yyyy')
,ScheduleString => 'FifthDayCycle'
,Iterations => 50
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('01/01/2006','mm/dd/yyyy')
,ScheduleString => 'March15thBilling'
,Iterations => 10
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('01/01/2006','mm/dd/yyyy')
,ScheduleString => 'SpecialClosingPeriod'
,Iterations => 12
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('01/01/2006','mm/dd/yyyy')
,ScheduleString => 'StandardEndOfMonth'
,Iterations => 10
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('01/01/2006','mm/dd/yyyy')
,ScheduleString => 'PeriodicalEndOfMonth'
,Iterations => 10
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('11/15/2006','mm/dd/yyyy')
,ScheduleString => 'ThursdaysOnlyBatchProcessing'
,Iterations => 4
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('11/15/2007','mm/dd/yyyy')
,ScheduleString => 'ThursdaysOnlyBatchProcessing'
,Iterations => 4
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('11/15/2008','mm/dd/yyyy')
,ScheduleString => 'ThursdaysOnlyBatchProcessing'
,Iterations => 4
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('11/15/2009','mm/dd/yyyy')
,ScheduleString => 'ThursdaysOnlyBatchProcessing'
,Iterations => 4
);
SYS.SHOW_CALENDAR_EVENTS(
InitialDate => TO_DATE('11/15/2010','mm/dd/yyyy')
,ScheduleString => 'ThursdaysOnlyBatchProcessing'
,Iterations => 4
);
END;
/
>>> Results:
>>>
Next 50 iterations of Schedule FifthDayCycle as of 01/01/2006 00:00:00
Iteration # 1 will run on 05-JAN-06 12.00.00.000000 AM
Iteration # 2 will run on 06-JAN-06 12.00.00.000000 AM
Iteration # 3 will run on 07-JAN-06 12.00.00.000000 AM
Iteration # 4 will run on 08-JAN-06 12.00.00.000000 AM
Iteration # 5 will run on 09-JAN-06 12.00.00.000000 AM
Iteration # 6 will run on 10-JAN-06 12.00.00.000000 AM
Iteration # 7 will run on 11-JAN-06 12.00.00.000000 AM
Iteration # 8 will run on 12-JAN-06 12.00.00.000000 AM
Iteration # 9 will run on 13-JAN-06 12.00.00.000000 AM
Iteration # 10 will run on 14-JAN-06 12.00.00.000000 AM
Iteration # 11 will run on 15-JAN-06 12.00.00.000000 AM
Iteration # 12 will run on 16-JAN-06 12.00.00.000000 AM
Iteration # 13 will run on 17-JAN-06 12.00.00.000000 AM
Iteration # 14 will run on 18-JAN-06 12.00.00.000000 AM
Iteration # 15 will run on 19-JAN-06 12.00.00.000000 AM
Iteration # 16 will run on 20-JAN-06 12.00.00.000000 AM
Iteration # 17 will run on 21-JAN-06 12.00.00.000000 AM
Iteration # 18 will run on 22-JAN-06 12.00.00.000000 AM
Iteration # 19 will run on 23-JAN-06 12.00.00.000000 AM
Iteration # 20 will run on 24-JAN-06 12.00.00.000000 AM
Iteration # 21 will run on 05-FEB-06 12.00.00.000000 AM
Iteration # 22 will run on 06-FEB-06 12.00.00.000000 AM
Iteration # 23 will run on 07-FEB-06 12.00.00.000000 AM
Iteration # 24 will run on 08-FEB-06 12.00.00.000000 AM
Iteration # 25 will run on 09-FEB-06 12.00.00.000000 AM
Iteration # 26 will run on 10-FEB-06 12.00.00.000000 AM
Iteration # 27 will run on 11-FEB-06 12.00.00.000000 AM
Iteration # 28 will run on 12-FEB-06 12.00.00.000000 AM
Iteration # 29 will run on 13-FEB-06 12.00.00.000000 AM
Iteration # 30 will run on 14-FEB-06 12.00.00.000000 AM
Iteration # 31 will run on 15-FEB-06 12.00.00.000000 AM
Iteration # 32 will run on 16-FEB-06 12.00.00.000000 AM
Iteration # 33 will run on 17-FEB-06 12.00.00.000000 AM
Iteration # 34 will run on 18-FEB-06 12.00.00.000000 AM
Iteration # 35 will run on 19-FEB-06 12.00.00.000000 AM
Iteration # 36 will run on 20-FEB-06 12.00.00.000000 AM
Iteration # 37 will run on 21-FEB-06 12.00.00.000000 AM
Iteration # 38 will run on 22-FEB-06 12.00.00.000000 AM
Iteration # 39 will run on 23-FEB-06 12.00.00.000000 AM
Iteration # 40 will run on 24-FEB-06 12.00.00.000000 AM
Iteration # 41 will run on 05-MAR-06 12.00.00.000000 AM
Iteration # 42 will run on 06-MAR-06 12.00.00.000000 AM
Iteration # 43 will run on 07-MAR-06 12.00.00.000000 AM
Iteration # 44 will run on 08-MAR-06 12.00.00.000000 AM
Iteration # 45 will run on 09-MAR-06 12.00.00.000000 AM
Iteration # 46 will run on 10-MAR-06 12.00.00.000000 AM
Iteration # 47 will run on 11-MAR-06 12.00.00.000000 AM
Iteration # 48 will run on 12-MAR-06 12.00.00.000000 AM
Iteration # 49 will run on 13-MAR-06 12.00.00.000000 AM
Iteration # 50 will run on 14-MAR-06 12.00.00.000000 AM
>>>
Next 10 iterations of Schedule March15thBilling as of 01/01/2006 00:00:00
Iteration # 1 will run on 23-FEB-06 12.00.00.000000 AM
Iteration # 2 will run on 23-FEB-07 12.00.00.000000 AM
Iteration # 3 will run on 24-FEB-08 12.00.00.000000 AM
Iteration # 4 will run on 23-FEB-09 12.00.00.000000 AM
Iteration # 5 will run on 23-FEB-10 12.00.00.000000 AM
Iteration # 6 will run on 23-FEB-11 12.00.00.000000 AM
Iteration # 7 will run on 24-FEB-12 12.00.00.000000 AM
Iteration # 8 will run on 23-FEB-13 12.00.00.000000 AM
Iteration # 9 will run on 23-FEB-14 12.00.00.000000 AM
Iteration # 10 will run on 23-FEB-15 12.00.00.000000 AM
>>>
Next 12 iterations of Schedule SpecialClosingPeriod as of 01/01/2006 00:00:00
Iteration # 1 will run on 28-JAN-06 12.00.00.000000 AM
Iteration # 2 will run on 25-FEB-06 12.00.00.000000 AM
Iteration # 3 will run on 28-MAR-06 12.00.00.000000 AM
Iteration # 4 will run on 27-APR-06 12.00.00.000000 AM
Iteration # 5 will run on 27-MAY-06 12.00.00.000000 AM
Iteration # 6 will run on 27-JUN-06 12.00.00.000000 AM
Iteration # 7 will run on 27-JUL-06 12.00.00.000000 AM
Iteration # 8 will run on 29-AUG-06 12.00.00.000000 AM
Iteration # 9 will run on 28-SEP-06 12.00.00.000000 AM
Iteration # 10 will run on 28-OCT-06 12.00.00.000000 AM
Iteration # 11 will run on 28-NOV-06 12.00.00.000000 AM
Iteration # 12 will run on 28-DEC-06 12.00.00.000000 AM
>>>
Next 10 iterations of Schedule StandardEndOfMonth as of 01/01/2006 00:00:00
Iteration # 1 will run on 31-JAN-06 12.00.00.000000 AM
Iteration # 2 will run on 28-FEB-06 12.00.00.000000 AM
Iteration # 3 will run on 31-MAR-06 12.00.00.000000 AM
Iteration # 4 will run on 30-APR-06 12.00.00.000000 AM
Iteration # 5 will run on 31-MAY-06 12.00.00.000000 AM
Iteration # 6 will run on 30-JUN-06 12.00.00.000000 AM
Iteration # 7 will run on 31-JUL-06 12.00.00.000000 AM
Iteration # 8 will run on 31-AUG-06 12.00.00.000000 AM
Iteration # 9 will run on 30-SEP-06 12.00.00.000000 AM
Iteration # 10 will run on 31-OCT-06 12.00.00.000000 AM
>>>
Next 10 iterations of Schedule PeriodicalEndOfMonth as of 01/01/2006 00:00:00
Iteration # 1 will run on 31-MAR-06 12.00.00.000000 AM
Iteration # 2 will run on 30-JUN-06 12.00.00.000000 AM
Iteration # 3 will run on 30-SEP-06 12.00.00.000000 AM
Iteration # 4 will run on 31-JAN-07 12.00.00.000000 AM
Iteration # 5 will run on 30-APR-07 12.00.00.000000 AM
Iteration # 6 will run on 31-JUL-07 12.00.00.000000 AM
Iteration # 7 will run on 31-OCT-07 12.00.00.000000 AM
Iteration # 8 will run on 29-FEB-08 12.00.00.000000 AM
Iteration # 9 will run on 31-MAY-08 12.00.00.000000 AM
Iteration # 10 will run on 31-AUG-08 12.00.00.000000 AM
>>>
Next 4 iterations of Schedule ThursdaysOnlyBatchProcessing as of 11/15/2006
00:00:00
Iteration # 1 will run on 16-NOV-06 12.00.00.000000 AM
Iteration # 2 will run on 30-NOV-06 12.00.00.000000 AM
Iteration # 3 will run on 07-DEC-06 12.00.00.000000 AM
Iteration # 4 will run on 14-DEC-06 12.00.00.000000 AM
>>>
Next 4 iterations of Schedule ThursdaysOnlyBatchProcessing as of 11/15/2007
00:00:00
Iteration # 1 will run on 29-NOV-07 12.00.00.000000 AM
Iteration # 2 will run on 06-DEC-07 12.00.00.000000 AM
Iteration # 3 will run on 13-DEC-07 12.00.00.000000 AM
Iteration # 4 will run on 20-DEC-07 12.00.00.000000 AM
>>>
Next 4 iterations of Schedule ThursdaysOnlyBatchProcessing as of 11/15/2008
00:00:00
Iteration # 1 will run on 20-NOV-08 12.00.00.000000 AM
Iteration # 2 will run on 04-DEC-08 12.00.00.000000 AM
Iteration # 3 will run on 11-DEC-08 12.00.00.000000 AM
Iteration # 4 will run on 18-DEC-08 12.00.00.000000 AM
>>>
Next 4 iterations of Schedule ThursdaysOnlyBatchProcessing as of 11/15/2009
00:00:00
Iteration # 1 will run on 19-NOV-09 12.00.00.000000 AM
Iteration # 2 will run on 03-DEC-09 12.00.00.000000 AM
Iteration # 3 will run on 10-DEC-09 12.00.00.000000 AM
Iteration # 4 will run on 17-DEC-09 12.00.00.000000 AM
>>>
Next 4 iterations of Schedule ThursdaysOnlyBatchProcessing as of 11/15/2010
00:00:00
Iteration # 1 will run on 18-NOV-10 12.00.00.000000 AM
Iteration # 2 will run on 02-DEC-10 12.00.00.000000 AM
Iteration # 3 will run on 09-DEC-10 12.00.00.000000 AM
Iteration # 4 will run on 16-DEC-10 12.00.00.000000 AM
/*
|| Listing 1.6: Create elements of an event-driven Scheduler Job
*/
BEGIN
-----
-- Add new default subscribers to the standard System
-- Alert Queue (SYS.ALERT_QUE)
-----
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'SYS.ALERT_QUE'
,subscriber => SYS.AQ$_AGENT(
name => 'AUX_EVENT_MONITOR_JOB'
,address => ''
,protocol => 0)
);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'SYS.ALERT_QUE'
,subscriber => SYS.AQ$_AGENT(
name => 'AUX_EVENT_MONITOR_SCH'
,address => ''
,protocol => 0)
);
-----
-- Grant the specified Oracle Streams AQ Internet agent
-- the privileges of the specified database user
-----
DBMS_AQADM.ENABLE_DB_ACCESS(
agent_name => 'AUX_EVENT_MONITOR_JOB'
,db_username => 'SYS'
);
DBMS_AQADM.ENABLE_DB_ACCESS(
agent_name => 'AUX_EVENT_MONITOR_SCH'
,db_username => 'SYS'
);
END;
/
-----
-- Create a new table to store alert text messages
-----
DROP TABLE sys.aux_event_logging PURGE;
CREATE TABLE sys.aux_event_logging (
log_dtm TIMESTAMP
,log_entry VARCHAR2(1000)
) TABLESPACE users;
-----
-- Create a new procedure for use by event-driven schedule
-----
CREATE OR REPLACE PROCEDURE sys.aux_event_monitor
AS
-----
-- Variables:
-- dequeue_options Record type that holds the options available
-- for the dequeue operation
-- message_properties Record type that holds information used to
-- convey the state of individual messages in
-- the queue
-- message Data structure to hold information about
-- database objects
-- message_handle Message handle; used for processing messages
-----
dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message ALERT_TYPE;
message_handle RAW(16);
BEGIN
-----
-- Set up dequeuing options:
-- 1.) The queue consumer is identified as the
-- AUX_EVENT_MONITOR_JOB object.
-- 2.) The queue consumer will not wait to consume the queue.
-- 3.) Once the consumer has navigated through the queue, the
-- queue's position is reset to the first enqueued message.
-- 4.) When the consumer has read the message, it is removed
-- from the queue.
-----
dequeue_options.consumer_name := 'AUX_EVENT_MONITOR_JOB';
dequeue_options.wait := DBMS_AQ.NO_WAIT;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.dequeue_mode := DBMS_AQ.REMOVE;
-----
-- Dequeue the message. The resulting message will be placed in
-- the message variable, which can then be interrogated.
-----
DBMS_AQ.DEQUEUE(
queue_name => 'SYS.ALERT_QUE'
,dequeue_options => dequeue_options
,message_properties => message_properties
,payload => message
,msgid => message_handle
);
-----
-- Insert selected parts of the message into the table that's
-- been prepared for its storage
-----
INSERT INTO sys.aux_event_logging (log_dtm, log_entry)
VALUES (
SYSTIMESTAMP
,DBMS_SERVER_ALERT.EXPAND_MESSAGE(
USERENV('LANGUAGE')
,message.message_id
,message.reason_argument_1
,message.reason_argument_2
,message.reason_argument_3
,message.reason_argument_4
,message.reason_argument_5
)
);
COMMIT;
END;
/
/*
|| Listing 1.7: Create Scheduler Schedule, Program, and Job Objects to
|| support Event-Scheduled example
*/
-----
-- Create a new Event Schedule object. This schedule will be invoked
-- whenever a new event is raised in SYS.ALERT_QUE, which is being
-- "watched" by the AUX_EVENT_MONITOR_SCH subscriber
-----
BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE(
schedule_name => 'SCH_FIRED_BY_EVENT'
,start_date => SYSTIMESTAMP
,event_condition => '1=1'
,queue_spec => 'SYS.ALERT_QUE,AUX_EVENT_MONITOR_SCH'
);
END;
/
-----
-- Create a new Program object that will execute the previously-
-- created AUX_EVENT_MONITOR stored procedure
-----
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'PGM_AUX_EVENT_MONITOR'
,program_action => 'AUX_EVENT_MONITOR'
,program_type => 'STORED_PROCEDURE'
,number_of_arguments => 0
,enabled => TRUE
);
END;
/
-----
-- Create a new Job object that incorporates the two prior Scheduler
-- objects
-----
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_AUX_EVENT_MONITOR'
,program_name => 'PGM_AUX_EVENT_MONITOR'
,schedule_name => 'SCH_FIRED_BY_EVENT'
,enabled => TRUE
,auto_drop => FALSE
);
END;
/
/*
|| Listing 1.8: Set up thresholds via DBMS_SERVER_ALERT to test
|| the triggering of the event-driven Job
*
-----
-- Change some warning and critical thresholds so that errors
-- will be logged quickly to the System Alert Queue:
-- Warning Critical
-- Warning Threshold Threshold
-- -------------------------------- --------- ---------
-- Blocked Users 1 2
-- User Commits Per Second 2 4
-----
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.BLOCKED_USERS
,warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,warning_value => 1
,critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,critical_value => 2
,observation_period => 1
,consecutive_occurrences => 1
,instance_name => 'orcl'
,object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SESSION
,object_name => NULL
);
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.USER_COMMITS_SEC
,warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,warning_value => 2
,critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,critical_value => 4
,observation_period => 1
,consecutive_occurrences => 1
,instance_name => 'orcl'
,object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM
,object_name => NULL
);
END;
//*
|| Listing 1.9: Demonstrate the event-driven Scheduler job by
|| causing threshold violations
*/
-----
-- Create table for "overpopulation" to raise alert
-----
DROP TABLE sys.overpopulation PURGE;
CREATE TABLE sys.overpopulation(
nbr NUMBER
)
TABLESPACE users;
INSERT INTO sys.overpopulation
VALUES(12345);
COMMIT;
-----
-- Run an anonymous PL/SQL block to force the number of User
-- Commits Per Second beyond the new warning threshold
-----
SET ECHO ON
BEGIN
FOR i IN 1..300
LOOP
FOR j IN 1..100
LOOP
UPDATE sys.overpopulation
SET nbr = 54321;
COMMIT;
END LOOP;
DBMS_LOCK.SLEEP(1);
END LOOP;
END;
/
-----
-- Run this DML statement from two different sessions to cause one
-- session to block the other
-----
UPDATE hr.employees
SET last_name = 'Highness'
WHERE employee_id = 101;
-----
-- Query the SYS.AUX_EVENT_LOGGING table to view the dequeued event information
-----
TTITLE 'Contents of Auxiliary Logging Table'
COL log_time FORMAT A20 HEADING 'Entry Logged At'
COL log_entry FORMAT A55 HEADING 'Log Entry' WRAP
SELECT
TO_CHAR(log_dtm, 'mm-dd-yyyy hh24:mi:ss') log_time
,log_entry
FROM sys.aux_event_logging
ORDER BY log_dtm DESC
;
TTITLE OFF
>>> Results:
Wed Feb 15 page 1
Contents of Auxiliary Logging Table
Entry Logged At Log Entry
-------------------- -------------------------------------------------------
02-15-2006 21:30:03 Metrics "User Commits Per Sec" is at .09975
02-13-2006 22:09:57 Metrics "User Commits Per Sec" is at 2.60063
02-13-2006 21:48:44 Session 147 is blocking 0 other sessions
02-13-2006 21:47:44 Metrics "User Commits Per Sec" is at .21735
02-13-2006 21:46:44 Metrics "User Commits Per Sec" is at 26.43649
02-13-2006 21:32:05 Session 147 is blocking 1 other sessions
02-13-2006 21:00:31 Metrics "User Commits Per Sec" is at .17572
02-13-2006 20:53:28 Metrics "User Commits Per Sec" is at 61.54357