ORACLE job creation and usage detailed

Oracle jobs have functions that are timed to perform tasks at a specified point in time or at a point in time each day. And after Oracle restarts, the job continues to run without restarting.

first, in the query system Job , you can query the view

--related views

SELECT * from Dba_jobs;

SELECT * from All_jobs;

SELECT * FROMUSER_JOBS;

--Query Field description

/*

field (column) type description

Unique identifier of the job number task

Log_user VARCHAR2 (30) the user who submitted the task

Priv_user VARCHAR2 (30) Users assigned permissions to the task

Schema_user VARCHAR2 (30) User mode for parsing a task

Last_date date the last time the task was successfully run

Last_sec VARCHAR2 (8) such as HH24:MM:SS format last_date date hours, minutes, and seconds

This_date date is running the start time of the task, or null if the task is not running

This_sec VARCHAR2 (8) such as HH24:MM:SS format this_date date hours, minutes, and seconds

Next_date date the next time the task is scheduled to run

Next_sec VARCHAR2 (8) such as HH24:MM:SS format next_date date hours, minutes, and seconds

Total_time number The total time required for the task to run, in seconds

Broken VARCHAR2 (1) flag parameter, y indicates that the task is interrupted and will not run later

INTERVAL VARCHAR2 (200) An expression used to calculate the next run time

Failures number task runs continuously without success

WHAT VARCHAR2 (2000) Pl/sql block to perform a task

Current_session_labelraw MLSLABEL the trusted Oracle session character for this task

Clearance_hi RAW MLSLABEL Oracle maximum gap that the task can trust

Clearance_lo RAW MLSLABEL Oracle minimum gap that the task can trust

Nls_env VARCHAR2 (2000) The NLS session settings that the task runs

Some other session parameters that the Misc_env RAW (32) task runs

*/

--Running job

SELECT * fromdba_jobs_running;

The most important field is the job, which is the ID number of the operation job, what the name of the operation stored procedure, next_date execution time, interval execution interval

Second, the implementation interval interval Operating Frequency

Describe interval parameter values

Every night 12 o'clock TRUNC (sysdate + 1)

Daily 8:30 A.M. TRUNC (sysdate + 1) + (8*60+30)/(24*60)

Every Tuesday noon 12 o'clock Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24

Midnight of the first day of the Month 12 O'Clock TRUNC (Last_day (sysdate) + 1)

Each quarter on the last day of the Evening 11 o'clock TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24

Every Saturday and 6:10 A.M. TRUNC (Next_day (sysdate, ' SATURDAY "), Next_day (Sysdate," SUNDAY "))) + (6X60+10)/(24x60)

Execute every second Interval => sysdate+ 1/(24 * 60 * 60)

If changed to Sysdate + 10/(24 *60 * 60) is 10 seconds of execution

Execute every Minute

Interval =>trunc (sysdate, ' mi ') + 1/(24*60)

If changed to Trunc (sysdate, ' mi ') + 10/(24*60) is performed every 10 minutes

perform regularly on a daily basis

Example: Daily 1 o'clock in the morning execution

Interval =>trunc (sysdate) + 1 +1/(24)

regular Weekly Execution

For example: Every Monday 1 o'clock in the morning execution

Interval =>trunc (Next_day (sysdate, ' Monday ') +1/24

Regular Monthly Execution

Example: Monthly 1st 1 o'clock in the morning

Interval=>trunc (Last_day (sysdate)) +1+1/24

timed every quarter

For example, the first day of every quarter, 1 o'clock in the morning execution

Interval =>trunc (Add_months (sysdate,3), ' Q ') + 1/24

execute every six months at regular intervals

For example: July 1 and January 1 every year 1 o'clock in the morning

Interval =>add_months (trunc (sysdate, ' yyyy '), 6) +1/24

Regular Annual Execution

For example: January 1 every year 1 o'clock in the morning execution

Interval=>add_months (Trunc (sysdate, ' yyyy '), 12) +1/24

Third, create Job Method

Create job, basic syntax:

Declare

Variable job number;

Begin

Sys.dbms_job.submit (Job =>: Job,

What => ' prc_name; '--name of the stored procedure executed

Next_date => to_date (' 22-11-201309:09:41 ', ' dd-mm-yyyy hh24:mi:ss '),

Interval => ' sysdate+1/86400 '); --86,400 seconds a day, that is, a second run prc_name process once

Commit

End

Using the Dbms_job.submit method procedure, this process has five parameters: job, what, next_date, interval, and No_parse.

Dbms_job.submit (

Job out Binary_ineger,

What in Varchar2,

Next_date in Date,

Interval in varchar2,

No_parse in Booean:=false)

A job parameter is an output parameter that is returned by the submit () procedure, which is used to uniquely identify a binary_ineger. General definition of a variable receive, you can go to the User_jobs view query job value.

The what parameter is the PL/SQL code block that will be executed, the stored procedure name, and so on.

The next_date parameter indicates when this work will be run.

interval parameter when this work will be executed again.

The no_parse parameter Indicates whether this work should be parsed--true when it is committed or executed, and the default value is False. Indicates that this pl/sql code should be parsed the first time it executes, and False indicates that the PL/SQL code should be parsed immediately.

Iv. Other Job Related stored Procedures

There are other processes in Dbms_job this package: broken, change, interval, isubmit, next_date, remove, run, submit, user_export, what;

Describe these processes in general:

1. The broken () process updates the status of a submitted work, typically used to mark a broken job as not broken. This process has three parameters: job, broken, and next_date.

Procedure Broken (

Job in Binary_integer,

Broken in Boolean,

Next_date in Date: = Sysdate

)

The job parameter is the work number, which uniquely identifies the work in the issue.

The broken parameter Indicates whether this work will be marked as broken--true indicates that the work will be marked as broken, and false indicates that the work will be marked as not broken.