Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational