Oracle Scheduling dba_scheduler_jobs View Oracle Tips by Burleson Consulting

Information about jobs can be displayed using the dba_scheduler_jobs view.  The following script uses this view to display information about currently defined jobs.

* jobs_10g.sql

-- Parameters:
--    1) Specific USERNAME or ALL which doesn't limit output.
-- *****************************************************************

set verify off

select
   owner,
   job_name,
   job_class,
   enabled,
   next_run_date,
   repeat_interval
from
   dba_scheduler_jobs
where
   owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
;

The output of the jobs_10g.sql script for the current user is displayed below.

SQL> @jobs_10g job_user

OWNER                          JOB_NAME                     JOB_CLASS                      ENABL
------------------------------ ------------------------------ ------------------------NEXT_RUN_DATE

---------------------------------------------------------------------------
REPEAT_INTERVAL

----------------------------------------------------------------------------

JOB_USER                       TEST_FULL_JOB_DEFINITION      DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.08.900000 +01:00
freq=hourly; byminute=0

JOB_USER                       TEST_PROG_SCHED_JOB_DEFINITION DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.16.200000 +01:00

JOB_USER                       TEST_PROG_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.09.600000 +01:00
freq=hourly; byminute=0

JOB_USER                       TEST_SCHED_JOB_DEFINITION      DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.16.200000 +01:00

4 rows selected.

When the test_stored_procedure_prog program is defined, a default argument value is specified.  The argument values of jobs that access predefined programs can be manipulated using the following procedures:

PROCEDURE set_job_argument_value (
  job_name                IN VARCHAR2,
  argument_position       IN PLS_INTEGER,
  argument_value          IN VARCHAR2)

PROCEDURE set_job_argument_value (
  job_name                IN VARCHAR2,
  argument_name           IN VARCHAR2,
  argument_value          IN VARCHAR2)

PROCEDURE set_job_anydata_value(
  job_name                IN VARCHAR2,
  argument_position       IN PLS_INTEGER,
  argument_value          IN SYS.ANYDATA)

PROCEDURE set_job_anydata_value(
  job_name                IN VARCHAR2,
  argument_name           IN VARCHAR2,
  argument_value          IN SYS.ANYDATA)

PROCEDURE reset_job_argument_value (
  job_name                IN VARCHAR2,
  argument_position       IN PLS_INTEGER)

PROCEDURE reset_job_argument_value (
  job_name                IN VARCHAR2,
  argument_name           IN VARCHAR2)

The parameters associated with these procedures and their usage are as follows:

* job_name - A name that uniquely identifies the job.

* argument_position - The position of the argument in the call specification.

* argument_name - The name of the argument.

* argument_value - The value assigned to the argument.

Arguments can be referenced by name or by position, and their values can be set or reset to the default value.  The example below shows how the argument values for a job can be reset:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name       => 'argument_job_definition',
    program_name   => 'test_stored_procedure_prog',

    enabled        => FALSE,
    comments       => 'Job defined by an existing program and schedule.');

  DBMS_SCHEDULER.set_job_argument_value (
    job_name       => 'argument_job_definition',
    argument_name  => 'p_text',
    argument_value => 'A different argument value.');

  DBMS_SCHEDULER.enable (
    name           => 'argument_job_definition');
END;
/

Information about job arguments can be displayed using the dba_scheduler_job_args view.  The following script uses this view to display argument information about a specified job.

* job_arguments.sql

-- Parameters:
--    1) Specific USERNAME or ALL which doesn't limit output.
--    2) Job name.
-- *****************************************************************

set verify off
column argument_name format a20
column value format a30

select
   argument_position,
   argument_name,
   value
from
   dba_scheduler_job_args
where
   owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
and
   job_name = upper('&2')
;

Using this script, one can see that the value of the job argument no longer matches the default value of the program argument.

SQL> @job_arguments job_user argument_job_definition

ARGUMENT_POSITION ARGUMENT_NAME        VALUE
----------------- -------------------- -----------------------------
                1 P_TEXT               A different argument value.

1 row selected.

Figure 2.10 shows the information displayed on the Scheduler Jobs screen in the OEM 10g DB Control.

Figure 2.10 ? OEM 10g DB Control: Scheduler Jobs

Jobs are normally run asynchronously under the control of the job coordinator, but they can also be controlled manually using the run_job and stop_job procedures.

PROCEDURE run_job (
  job_name                IN VARCHAR2,
  use_current_session     IN BOOLEAN DEFAULT TRUE)

PROCEDURE stop_job (
  job_name                IN VARCHAR2,
  force                   IN BOOLEAN DEFAULT FALSE)

The parameters associated with these procedures and their usage are as follows:

* job_name - A name that identifies a single job, a job class or a comma separated list of job names.

* use_current_session - When set to TRUE, the job is run in the user?s current session; otherwise, a job slave runs it in the background.

* force - When set to FALSE, a job is stopped using the equivalent of sending a ctrl-c to the job.  When TRUE, a graceful shutdown is attempted, but if this fails, the slave process is killed.  Using the force parameter requires the user to have the MANAGE SCHEDULER system privilege.

The following code shows how the procedures can be used:

BEGIN
  -- Run job synchronously.
  DBMS_SCHEDULER.run_job (
    job_name            => 'test_full_job_definition',
    use_current_session => FALSE);

  -- Stop jobs.
  DBMS_SCHEDULER.stop_job (
    job_name => 'test_full_job_definition, test_prog_sched_job_definition');
END;
/

A new job can be created as a replica of an existing job using the copy_job procedure.  The new job is created in the disabled state, with the old job remaining unchanged.

PROCEDURE copy_job (
  old_job                 IN VARCHAR2,
  new_job                 IN VARCHAR2)

The parameters associated with this procedure and their usage are as follows:

* old_job - The name of the job whose attribute will be duplicated to create the new job.

* new_job - The name of the duplicate job to be created.

An example of its usage might be as follows:

BEGIN
  DBMS_SCHEDULER.copy_job (
    old_job => 'test_full_job_definition',
    new_job => 'new_test_full_job_definition');
END;
/

Jobs can be deleted using the drop_job procedure listed blow.

PROCEDURE drop_job (
  job_name                IN VARCHAR2,
  force                   IN BOOLEAN      DEFAULT FALSE)

The parameters associated with this procedure and their usage are as follows:

* job_name - A name that identifies a single job, a job class name or a comma separated list.

* force - TRUE running jobs are stopped before the job is dropped.  When FALSE, dropping a job that is running will fail.

The following code shows how the drop_job procedure can be used:

BEGIN
  DBMS_SCHEDULER.drop_job (
   job_name => 'test_full_job_definition, test_prog_sched_job_definition,
                test_prog_job_definition, test_sched_job_definition,
                argument_job_definition',
   force    => TRUE);
END;
/

The output from the jobs_10g.sql script shows that the jobs have been removed.

SQL> @jobs_10g job_user

no rows selected

The following section will focus on job classes, which are the first of the scheduler administration objects.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.