Oracle Job Scheduling - Event Based Scheduling - Part 2

Author: Packt Publishing Published on: 3rd Nov 2009

The following objects show up in the schema, which are created to support the queue table:

Ads

This also explains why MARVIN needs quota on his default tablespace.

The queue definitions part is ready. Now, we can tie a job to the queue. First, create a job as follows:

Sample Code

--/
BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"MARVIN"."BCKUP_01"',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/bin/rman.sh',
event_condition => 'tab.user_data.msg=''GO''',
queue_spec => '"MARVIN"."BCKUP_Q"',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"LONGER"',
comments => 'backup a database',
auto_drop => FALSE,
number_of_arguments => 1,
enable => FALSE
);
sys.dbms_scheduler.set_attribute
(
name => '"MARVIN"."BCKUP_01"',
attribute => 'raise_events',
value => dbms_scheduler.job_started +
dbms_scheduler.job_succeeded +
dbms_scheduler.job_failed +
dbms_scheduler.job_broken +
dbms_scheduler.job_completed +
dbms_scheduler.job_stopped +
dbms_scheduler.job_sch_lim_reached +
dbms_scheduler.job_disabled +
dbms_scheduler.job_chain_stalled
);
sys.dbms_scheduler.set_job_argument_value
(
job_name => '"MARVIN"."BCKUP_01"',
argument_position => 1,
argument_value => 'db_01'
);
DBMS_SCHEDULER.SET_ATTRIBUTE
(
name => '"MARVIN"."BCKUP_01"',
attribute => 'destination',
value => 'pantzer:15021'
);
DBMS_SCHEDULER.SET_ATTRIBUTE
(
name => '"MARVIN"."BCKUP_01"',
attribute => 'credential_name',
value => '"MARVIN"."JOBS_CRED2"'
);
END;
/

This is just a simple remote external job that calls an RMAN script with an argument for the database to back up. As the backup will take longer than a few seconds, it looks obvious to put it in the job_class called LONGER that we defined a while ago. The queue that is coupled to this job is the queue we defined before. It is bckup_q as defined by the queue_spec parameter. As soon as the GO message appears in the payload of the queue, all of the jobs that listen to this queue and those waiting for this GO message will get started. The code listed for the MARVIN job can also be put together using DB Console.

In the following Schedule screen, select Event as Schedule Type:

As the job was not Enabled, it now looks like the following: