Moderate Resolution of Oracle 10g DBMS_SCHEDULER

This forum article (CCID technical community) provides a detailed introduction to the usage of DBMS_SCHEDULER. For more information, see the following:

DBMS_SCHEDULER is a new package in Oracle 10 Gb. Compared with the old version of dbms_job package, dbms_scheduler has many new features. I will introduce how to use this package through a series of articles.

1. Create a job

What is a job? Simply put, schedule is added with the task description. There are also some required parameters.

The "task" mentioned here can be a stored procedure in the database, an anonymous PL/SQL block, or an operating system-level script.

You can define a "plan" in two ways ":

1) Use DBMS_SCHDULER.CREATE_SCHEDULE to define a plan;

2) Call the DBMS_SCHDULER.CREATE_JOBE process and specify it directly (details will be given below)

When creating a plan, you must specify at least the following attributes, which are required for job running:

Start Time (start_time );

Repeat_interval );

End Time (end_time)

In addition, there are many additional parameters for a job:

Job_class

Job_priority

Auto_drop

Restartable

Max_runs

Max_failures

Schedule_limit

Logging_level

Next, I will explain it in the form of Q &.

Q1: How can I query job attributes in a database?

A1: There are two methods:

1) query (DBA | ALL | USER) _ SCHEDULER_JOBS View

(Tip: Select DBA | ALL | USER view based on different USER permissions)

2) Call the GET_ATTRIBUTE process in the DBMS_SCHEDULER package

Q2: how to set these attributes?

A2: There are two methods

1) specify

2) Call the SET_ATTRIBUTE process in the DBMS_SCHEDULER package

Q3: "What permissions do I need to create a job "?

It can create jobs with the owner as any user (except SYS users.

By default, a job is created in the current schema and is not activated.

The enabled attribute needs to be explicitly set to true. Let's look at an example:

Begin

Dbms_scheduler.create_job

(

Job_name => ARC_MOVE,

Schedule_name => EVERY_60_MINS,

Job_type => EXECUTABLE,

Job_action =>/home/dbtools/move_arcs.sh,

Enabled => true,

Comments => Move Archived Logs to a Different Directory

);

End;

/

Q4: Can you elaborate on the parameters used in the above process?

A4:

Job_name: As the name implies, each job must have a name.

Schedule_name: If a scheduler is defined, specify the scheduler name here.

Job_type: Currently, three types are supported:

PL/SQL block: PLSQL_BLOCK,

Stored Procedure: STORED_PROCEDURE

External program: EXECUTABLE (an external program can be a shell script or an operating system-level command ).

Job_action: job_action has different meanings depending on job_type.

If job_type specifies a stored procedure, you must specify the name of the stored procedure;

If job_type specifies PL/SQL blocks, you must enter the complete PL/SQL code;

If the external program specified by job_type is used, enter the script name or the operating system command name.

Enabled: As mentioned above, it specifies whether the job is automatically activated after creation.

Comments: a brief description of the job

2. Specify the execution frequency of the job

If we create a job and want it to run according to the specified date and time, we need to define the repetition frequency of the job. for example, run every day, run at on every Sunday, run on every Monday, Wednesday, and run on the last Sunday of each year.

(Note: In versions earlier than 10 Gb, the interaction with the operating system is not very good. For example, to implement a regular rman backup task, you must use the OS command to implement it. You can use crontab in UNIX and AT in windows)

10 Gb has been greatly enhanced in this regard, because the operating system commands or scripts can be directly specified during job creation, and then the execution frequency of the job can be reasonably defined, complex scheduling tasks can be easily completed.

10G supports two repeat_interval modes, one is the PL/SQL expression, which is also used in the dbms_job package, such as SYSDATE + 1, SYSDATE + 30/24*60; the other is the calendar expression.

For example, MON indicates Monday, SUN indicates Sunday, DAY indicates everyday, WEEK indicates weekly, and so on. Here are several examples using calendar expressions:

Repeat_interval => FREQ = HOURLY; INTERVAL = 2

Run a job every two hours.

Repeat_interval => FREQ = DAILY

Run a job once a day

Repeat_interval => FREQ = WEEKLY; BYDAY = MON, WED, FRI"

1, 3, and 5 jobs per week

Repeat_interval => FREQ = YEARLY; BYMONTH = MAR, JUN, SEP, DEC; BYMONTHDAY = 30

Run the job on the 30th, every year.

People who have used crontab should feel familiar with each other.

The following describes the rules for using calendar expressions:

The calendar expression is basically divided into three parts: The first part is the frequency, that is, the keyword "FREQ", which must be specified; the second part is the time INTERVAL, that is, the keyword "INTERVAL, value Range: 1-999. it is an optional parameter. The last part is an additional parameter that can be used to specify the exact date and time. It is also an optional parameter. For example, the following values are valid:

BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY

BYHOUR, BYMINUTE, BYSECOND

For detailed parameter descriptions, see dbms_scheduler instructions.

Now that repeat_interval is mentioned, you may ask: "Is there a simple way to get it, or evaluate the running time of a job and the next running time? "

The dbms_scheduler package provides a evaluate_calendar_string process, which can easily meet this requirement. See the following example:

SQL> set serveroutput on size 999999

SQL> declare

Rochelle start_date TIMESTAMP;

Rochelle next_date TIMESTAMP;

Rochelle return_date TIMESTAMP;

Begin

Rochelle start_date: = trunc (SYSTIMESTAMP );

Rochelle return_date: = l_start_date;

For ctr in 1 .. 10 loop

Dbms_scheduler.evaluate_calendar_string (

FREQ = DAILY; BYDAY = MON, TUE, WED, THU, FRI; BYHOUR = 7,15,

Rochelle start_date, Rochelle return_date, Rochelle next_date

);

Dbms_output.put_line (Next Run on: |

To_char (l_next_date, mm/dd/yyyy hh24: mi: ss)

);

Rochelle return_date: = l_next_date;

End loop;

End;

/

The output result is as follows:

Next Run on: 03/22/2004 07:00:00

Next Run on: 03/22/2004 15:00:00

Next Run on: 03/23/2004 07:00:00

Next Run on: 03/23/2004 15:00:00

Next Run on: 03/24/2004 07:00:00

Next Run on: 03/24/2004 15:00:00

Next Run on: 03/25/2004 07:00:00

Next Run on: 03/25/2004 15:00:00

Next Run on: 03/26/2004 07:00:00

Next Run on: 03/26/2004 15:00:00