Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle CREATE_SCHEDULE Procedure
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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 later.

  • 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 DB Control via the Create Schedule screen shown in Figure 11.16.

 

Figure 11.16 – OEM 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 does not 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

 

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

 

Figure 11.17 – OEM 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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.