/*
|| 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