Oracle job procedure Stored procedure timed tasks

The Oracle job has timed execution and can perform its own tasks at a specified point in time or at some point in the day.

First, query the system job, you can query the view

--related views

SELECT * from Dba_jobs;

SELECT * from All_jobs;

SELECT * from User_jobs;

--Query Field description

/*

Field (column)          type                 description

          Unique identifier of the job number task

Log_user           VARCHAR2 (in)    the user who submitted the task

Priv_user          VARCHAR2 (in)    assign a task permission to a user

Schema_user        VARCHAR2 ()    user mode for syntactic analysis of tasks

Last_date          Date            The last time the task was successfully run

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

This_date          Date The            start time of the task is running, or null if no task is running

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

Next_date          Date            time of the next scheduled run of the task

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

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

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

INTERVAL           VARCHAR2 ($) An   expression for calculating the next run time

Failures     number task runs with no successive successes

What               VARCHAR2 (a)  PL/SQL block to perform a task

Current_session_label RAW          MLSLABEL Trust Oracle Session break 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 (a)   task run NLS session settings

Misc_env              RAW (32)          

*/

--Running job

SELECT * from Dba_jobs_running;

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

Second, the execution interval interval running 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 each month 12 o'clock TRUNC (Last_day (sysdate) + 1)
Last day of each quarter 11 o'clock TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24
Every Saturday and Sunday 6:10 A.M. TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60)

Execution times per second

Interval = sysdate + 1/(24 * 60 * 60)

If you change to Sysdate + 10/(24 * 60 * 60) is 10 seconds to execute

Execute every Minute
Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)

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

Regular execution every day
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
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24

Quarterly Scheduled execution
For example, the first day of each quarter is 1 o'clock in the morning execution
Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 1/24

Every half-yearly scheduled execution
For example: Every July 1 and January 1 1 o'clock in the morning
Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +1/24

Scheduled execution every year
For example: January 1 1 o'clock in the morning every year to execute
Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24

Iii. creating a Job method

Create Job,
Basic syntax:

Declare

    Variable job number;

Begin

    Sys.dbms_job.submit (Job = job,

    what = ' prc_name; ',

    Next_date = To_date (' 22-11-2013 09:09:41 ', ' dd-mm-yyyy hh24:mi:ss '),

    Interval = ' sysdate+1/86400 ');--86,400 seconds per day, that is, one second. Run the prc_name process once

    Commit

End

Using the Dbms_job.submit method process, 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)

The job parameter is an output parameter, returned by the submit () procedure, which is used to uniquely identify a work binary_ineger. Generally define a variable to receive, you can go to User_jobs view Query job value.
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 the job will run.
Interval parameter when this work will be re-executed.
The No_parse parameter indicates whether this work should be parsed--true at the time of submission or execution, and the default value is False. Indicates that this PL/SQL code should parse the first time it executes, while false indicates that this PL/SQL code should be parsed immediately.

Iv. other job-related stored procedures

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

These procedures are outlined below:

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 problem.
The broken parameter indicates whether this work will be marked as broken--true this work will be marked as broken, and false indicates that the work will be marked as not broken.
The Next_date parameter indicates when this work will run again. The default value for this parameter is the current date and time.

If for some reason the job fails to execute successfully, Oracle will retry 16 times, fail to execute successfully, will be marked as broken, restart the job with broken status, as in the next two ways;
A. Use Dbms_job.run () to execute the job immediately
Begin
Dbms_job.run (: Job)-This job is the job number returned when the submit process is submitted or dba_jobs to find the corresponding job
End

B. Use Dbms_job.broken () to re-mark broken as false
Begin
Dbms_job.broken (: Job, False, Next_date)
End

2. The change () process is used to alter the settings of the specified job.
This process has four parameters: job, what, next_date, interval.
Procedure Change (
Job in Binary_integer,
What in Varchar2,
Next_date in Date,
Interval in VARCHAR2
)
This job parameter is an integer value that uniquely identifies the job.
What parameter is a PL/SQL code block that is run by this job.
The Next_date parameter indicates when this job will be executed.
The interval parameter indicates the frequency of a job re-execution.

3. The interval () procedure is used to explicitly set the number of intervals between executions of a job.
This process has two parameters: job, Interval.
Procedure interval (
Job in Binary_integer,
Interval in VARCHAR2
)
The job parameter identifies a specific job.
The interval parameter indicates the frequency of a work re-execution.

4. The Isubmit () process is used to submit a job with a specific job number.
This process has five parameters: job, what, Next_date, interval, no_parse.
Procedure Isubmit (
Job in Binary_ineger,
What in Varchar2,
Next_date in Date,
Interval in varchar2,
No_parse in Booean: = FALSE
)
The only difference between this process and the submit () process is that the job parameter is passed as an in parameter and includes a job number provided by the developer. If the supplied job number is already in use, an error is generated.

5. The next_date () process is used to explicitly set the execution time of a job. This process receives two parameters: Job, next_date.
Procedure Next_date (
Job in Binary_ineger,
Next_date in Date
)
The job identifies a work that already exists.
The Next_date parameter indicates the date and time that this job should be executed.

6. Remove () procedure to delete a job that is scheduled to run. This process takes a parameter:
Procedure remove (job in Binary_ineger);
The job parameter uniquely identifies a job the value of this parameter is the value of the job parameter that is returned by the submit () procedure called for this work, and the job that is already running cannot be deleted.

7. The run () procedure is used to execute a specified job immediately. This procedure receives only one parameter:
Procedure run (Job in Binary_ineger)
The job parameter identifies the work that will be performed immediately.

8. Using the Submit () process, the job is normally planned. above to tell

9. The User_export () procedure returns a command that is used to schedule an existing job so that the job can be resubmitted. This program has two parameters: job, My_call.
Procedure User_export (
Job in Binary_ineger,
My_call in Out VARCHAR2
)
The job parameter identifies an assigned job.
The My_call parameter contains the body that is required to resubmit the job in its current state.

10. What () process promises to reset this running command at job execution time. This process receives two parameters: job, what.
Procedure what (
Job in Binary_ineger,
What in Out VARCHAR2
)
The job parameter identifies a work that exists.
What parameter indicates the new PL/SQL code that will be executed. Implemented function: Automatically inserts the current system time into the Getsysdate table every minute.

V. Examples

/* Insert one time per 10 seconds */

--Create TABLE

CREATE TABLE Tab_time (

       Current_time Timestamp       

);

--Create a stored procedure

Create or replace procedure Pro_job_print

As

   Begin

       --dbms_output.put_line (' System time: ' | | to_char (sysdate, ' dd-mm-yyyy hh24:mi:ss '));

       INSERT into tab_time values (sysdate);

   End

--Call the process test   

Begin

   Pro_job_print;   

End

--select * from dual;   

--Create Job

Declare      

   JOB1 number;

Begin

   Dbms_job.submit (Job1, ' pro_job_print; ', sysdate, ' sysdate+10/86400 ');--insert one record per 10

End

--related views

SELECT * from Dba_jobs;

SELECT * from All_jobs;

SELECT * from User_jobs;

--Running job

SELECT * from Dba_jobs_running;

--Run job

Begin

   Dbms_job.run (26);--and select * from User_jobs; The job value in the corresponding, see what the corresponding process

--Query whether to insert data

Select To_char (current_time, ' dd-mm-yyyy hh24:mi:ss ') Current_time from Tab_time order by Current_time;

--Delete a job

Begin

   Dbms_job.remove (26);--and select * from User_jobs; The job value in the corresponding, see what the corresponding process

End      

Vi. about setting the number of job tasks and controlling concurrency

Initialize related parameters job_queue_processes
alter system set job_queue_processes = max scope = spfile;//Maximum value cannot exceed 1000;
Job_queue_interval = 10; Schedule job refresh frequency in seconds

Job_queue_process represents the number of jobs that Oracle can have concurrently, which can be sqlplus through statements
Show parameter job_queue_process; To view the values of job_queue_process in Oracle.

SELECT * from V$parameter;

Select name, description from v$bgprocess;

When a job_queue_process value of 0 indicates that all Oracle's jobs are stopped, the statement
alter system set job_queue_processes = 10;
To adjust the job that started Oracle.

If you set the value of job_queue_processes to 1, it is a serial run, that is, a quick switch to perform a job task.

Seven, the job does not run the approximate reason

(1), the above explained the job parameters: job-related parameters One is job_queue_processes, this is the number of processes running the job, of course, the system inside the job is larger than this value, there will be queued, the minimum is 0, indicating that the job is not running, The maximum value is 1000, on the OS corresponding to the process snpn,9i after the OS management job is called CJQN. You can use the following SQL to determine how many snp/cjq are currently running.
SELECT * from V$bgprocess, this paddr non-empty SNP/CJQ process is the currently idle process, and some indicates a working process.
The other is job_queue_interval, in the range between 1--3600, the unit is the second, this is the wake-up job process, because every time the SNP run he rested, need to wake him regularly, this value can not be too small, too small can affect the performance of the database.

First determine whether the above two parameters are set correctly, especially the first parameter, set to 0, all jobs will not automatically run.

(2), using the following SQL to view the job's broken,last_date and Next_date,last_date is the last time the job ran successfully, Next_date is the next execution time calculated based on the frequency of the set, According to this information can determine whether the job last normal, but also to determine the next time is right, SQL is as follows:
SELECT * from Dba_jobs;
Sometimes we find his next_date is January 1, 4000, indicating that the job is either in the running, or the state is break (broken=y), if the broken value of the job is found to be Y, to find the user to understand, Determine whether the job can be broken, if not broken, then change the broken value to N, modify and then use the above SQL view to find its last_date has changed, the job can run normally, modify the broken state of SQL as follows:

Begin
Dbms_job. Broken (<JOB_ID>, FALSE);
End

(3), use the following SQL query whether the job is still running
SELECT * from Dba_jobs_running;
If you find that the job has run for a long time and is not over, check the reason. General job running lock related resources, you can view the v$access and v$locked_object the two view. If other processes are found to lock the job-related object, including resources such as package/function/procedure/table, then the other processes should be deleted, if necessary, the job process is also deleted, and then re-execute to see the results.

(4), if the above are normal, but the job is not run, how to do? Then we have to consider restarting the job process to prevent the SNP process from dying and causing the job not to run, the instructions are as follows:
alter system set job_queue_processes = 0; --Shut down the job process and wait 5--10 seconds
alter system set job_quene_processes = 5; --Restore the original value

(5), Oracle bug:oracle9i There is a bug, when the counter to 497 days, just reach its maximum, then the count will become-1, continue counting becomes 0, and then the counter will no longer run. If this is the case, you will have to restart the database, but the other Oracle7345 and oracle8i databases do not see the problem.

(6), the database on the check basically this much, if the job is still a problem, it needs to look at whether it is the program itself, such as the amount of data processing, or slow network speed and other causes of the operation is too long, it needs specific analysis of specific circumstances. We can do the job manually through the following SQL to see:

Begin
Dbms_job.run (<job>_id)
End
If you find that job execution is not normal, you need to combine the program to analyze the specific.

Oracle job procedure Stored procedure timed tasks