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 Create Schedules

Oracle Tips by Burleson Consulting

Schedules

The create_schedule procedure defines the start time, end time and interval that can be applied to a job.

PROCEDURE create_schedule (
  schedule_name           IN VARCHAR2,
  start_date              IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
  repeat_interval         IN VARCHAR2,
  end_date                IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
  comments                IN VARCHAR2                  DEFAULT NULL)

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

* schedule_name - A name that uniquely identifies the schedule.

* start_date - The date when this schedule will take effect.  This date may be in the future if scheduled jobs are set up in advance.

* repeat_interval - The definition of how often the job should execute.  A value of NULL indicates that the job should only run once. The repeat interval is defined using a calendaring syntax, which is new to Oracle 10g.  This will be explained in more detail in a later chapter.

* end_date - The date when this schedule will stop.  This combined with the start_date parameter enables a job to be scheduled for a finite period of time.

* comments - Free text, allowing the user to record additional information.

The following code segment defines a new schedule that runs every hour on minute ?0?.  The lack of an end_date parameter value means that the job will repeat forever based on the interval.

BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_hourly_schedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    comments        => 'Repeats hourly, on the hour, for ever.');
END;
/

Schedules are created in the OEM 10g DB Control via the Create Schedule screen shown in Figure 2.6.

Figure 2.6 ? OEM 10g DB Control: Create Schedule

Information about schedules can be displayed using the dba_scheduler_schedules view.  The following script uses this view to display information about schedules for a specified user or all users.

* schedules.sql

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

set verify off

select
   owner,
   schedule_name,
   repeat_interval
from
   dba_scheduler_schedules
where
   owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
;

The following is an example of output from the schedules.sql script.

SQL> @schedules job_user

OWNER                          SCHEDULE_NAME
------------------------------ ------------------------------
REPEAT_INTERVAL
-------------------------------------------------------------
JOB_USER                       TEST_HOURLY_SCHEDULE
freq=hourly; byminute=0

1 row selected.

Alternately, the Scheduler Schedules screen of the OEM 10g DB Control, shown in Figure 2.7, can be used to display schedule information.

Figure 2.7 ? OEM 10g DB Control: Scheduler Schedules

Schedules can be dropped using the drop_schedule procedure, whose call specification is listed below.

PROCEDURE drop_schedule (
  schedule_name           IN VARCHAR2,
  force                   IN BOOLEAN      DEFAULT FALSE)

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

* schedule_name - A name that identifies a single schedule or a comma separated list of schedule names.

* force - If set to TRUE, all jobs and windows which reference this schedule are disabled prior to the schedule being dropped.  If set to FALSE, the presence of dependants will produce errors.

The following examples show how the drop_schedule procedure is used:

BEGIN
  DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE');
END;
/

The output from the schedules.sql script shows that the schedule has been removed.

SQL> @schedules job_user

no rows selected

Now that details on how to define reusable objects such as programs and schedules have been presented, the following section will show how they are used to schedule jobs.

Jobs

Jobs are what the scheduler is all about.  They are created using the create_job procedure, which is overloaded allowing a job to be defined in one of four ways:

* Completely self-contained, with the program and schedule defined inline.

* Referencing both a predefined program and schedule.

* Referencing a predefined program, but with an inline schedule.

* Referencing a predefined schedule, but with an inline program.

The overloaded call specifications are listed below.

PROCEDURE create_job (
  job_name                IN VARCHAR2,
  job_type                IN VARCHAR2,
  job_action              IN VARCHAR2,
  number_of_arguments     IN PLS_INTEGER              DEFAULT 0,
  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  repeat_interval         IN VARCHAR2                 DEFAULT NULL,
  end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  job_class               IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
  enabled                 IN BOOLEAN                  DEFAULT FALSE,
  auto_drop               IN BOOLEAN                  DEFAULT TRUE,
  comments                IN VARCHAR2                 DEFAULT NULL)

PROCEDURE create_job (
  job_name                IN VARCHAR2,
  program_name            IN VARCHAR2,
  schedule_name           IN VARCHAR2,
  job_class               IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
  enabled                 IN BOOLEAN                  DEFAULT FALSE,
  auto_drop               IN BOOLEAN                  DEFAULT TRUE,
  comments                IN VARCHAR2                 DEFAULT NULL)

PROCEDURE create_job (
  job_name                IN VARCHAR2,
  program_name            IN VARCHAR2,
  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  repeat_interval         IN VARCHAR2                 DEFAULT NULL,
  end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  job_class               IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
  enabled                 IN BOOLEAN                  DEFAULT FALSE,
  auto_drop               IN BOOLEAN                  DEFAULT TRUE,
  comments                IN VARCHAR2                 DEFAULT NULL)

PROCEDURE create_job (
  job_name                IN VARCHAR2,
  schedule_name           IN VARCHAR2,
  job_type                IN VARCHAR2,
  job_action              IN VARCHAR2,
  number_of_arguments     IN PLS_INTEGER              DEFAULT 0,
  job_class               IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
  enabled                 IN BOOLEAN                  DEFAULT FALSE,
  auto_drop               IN BOOLEAN                  DEFAULT TRUE,
  comments                IN VARCHAR2                 DEFAULT NULL)

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

* job_name - A name that uniquely identifies the job.

* job_type - The type of action associated with this job (PLSQL_BLOCK, STORED_PROCEDURE or EXECUTABLE).

* job_action - The actual work that is done by the job.

* number_of_arguments - The number of arguments required by this job.  Programs which use arguments must have their arguments defines before they can be enabled.

* start_date - The date when this schedule will take effect.  This may be in the future if scheduled jobs are setup in advance.

* repeat_interval - The definition of how often the job should execute.  A value of NULL indicates that the job should only run once. The repeat interval is defined using a PL/SQL expression or the calendaring syntax, which is new to Oracle 10g.  This will be explained in more detail in a later chapter.

* end_date - The date when this schedule will stop.  This combined with the start_date parameter enables a job to be scheduled for a finite period of time.

* job_class - The job class associated with this job.  If no job_class is defined, the DEFAULT_JOB_CLASS is assigned.

* enabled - A flag which indicates if the job is enabled or not.  If the job accepts arguments, it cannot be enabled until the arguments are defined.

* auto_drop - Indicates if the job should be dropped once it has run for the last time.

* comments - Free text, allowing the user to record additional information.

* schedule_name - The name of the schedule, window or window group used to define the job schedule.

* program_name - The name of the program which defines the action of the job.

The following code examples rely on the previously defined programs and schedules to show how the overloads of the create_job procedure are used.

BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
BEGIN
  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');
END;
/
BEGIN
  -- Job defined by an existing program and inline schedule.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_prog_job_definition',
    program_name    => 'test_plsql_block_prog',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined by existing program and inline schedule.');
END;
/
BEGIN
  -- Job defined by existing schedule and inline program.
  DBMS_SCHEDULER.create_job (
     job_name      => 'test_sched_job_definition',
     schedule_name => 'test_hourly_schedule',
     job_type      => 'PLSQL_BLOCK',
     job_action    => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
     enabled       => TRUE,
     comments      => 'Job defined by existing schedule and inline program.');
END;
/

The generate_job_name function can be used to generate a unique name for a job.

FUNCTION generate_job_name (
   prefix        IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2

A sequence number is appended to the specified job name prefix to guarantee uniqueness.  If the prefix is not specified, a standard prefix is used.  The query below shows how it can be used:

column job_name_1 format a20
column job_name_2 format a20

select
   DBMS_SCHEDULER.generate_job_name ('test_job') as job_name_1,
   DBMS_SCHEDULER.generate_job_name as job_name_2
from
   dual;

JOB_NAME_1           JOB_NAME_2
-------------------- --------------------
TEST_JOB6            JOB$_7

1 row selected.

Figures 2.8 and 2.9 show the Create Job (General) and Create Job (Schedule) screens respectively.  These provide a web-based alternative to the create_job procedure.

Figure 2.8 ? OEM 10g DB Control: Create Job (General)

Figure 2.9 ? OEM 10g DB Control: Create Job (Schedule)

 

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