About Oracle Dbms_job timed execution of content.

First, set the initialization parameters job_queue_processes
Sql> alter system set job_queue_processes=n; (n>0)
Job_queue_processes Maximum value is 1000
View the job queue background process
Sql>select name,description from V$bgprocess;
or sql>show parameter job_queue_processes;
Ii. Introduction of Dbms_job Package usage
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,
Brokenin BOOLEAN,
Next_date in DATE: = Sysdate
)
The job parameter is the work number, which uniquely identifies the work.
The broken parameter indicates whether this work will be marked as broken--true this work will be marked as broken, and Flase 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
Sql>begin
Sql>dbms_job.run (: Jobno)--The job number returned when the Jobno is submitted for the submit process
sql>end;
sql>/
B. Use Dbms_job.broken () to re-mark broken as false
Sql>begin
Sql>dbms_job.broken (: job,false,next_date)
sql>end;
sql>/
2. The change () process is used to alter the settings of the specified work.
This process has four parameters: job, what, next_date, and 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 operation.
The Next_date parameter indicates when this work will be performed.
The interval parameter indicates the frequency of a work re-execution.
3. The Interval () procedure is used to explicitly set the number of intervals between the re-execution of a work. This process has two parameters: Job and 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 work number. This process has five parameters: job, what, next_date, interval, and 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 work number provided by the developer, and an error occurs if the supplied work number is already in use.
5. The next_date () process is used to explicitly set the execution time of a job. This process receives two parameters: Job and 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 work should be performed.
6. Remove () procedure to delete a work that has been 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 returned by the submit () procedure called for this work, and the work that is already running cannot be deleted by the calling program.
7. The Run () procedure is used to perform 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, the use of the submit () process, the work is normally planned.
This process has five parameters: job, what, next_date, interval, and No_parse.
PROCEDURE SUBMIT
(JOB out Binary_ineger,
What in VARCHAR2,
Next_date in DATE,
INTERVAL in VARCHAR2,
No_parse in Booean: = FALSE
)
The job parameter is the Binary_ineger returned by the submit () procedure; This value is used to uniquely identify a job.
What parameter is the PL/SQL code block that will be executed.
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 at the time of submission or execution, and true indicates that this PL/SQL code should parse the first time it executes, while false indicates that this PL/SQL code should parse immediately.
9. The User_export () procedure returns a command that is used to schedule an existing work so that the work can be resubmitted.
This program has two parameters: Job and 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 this work in its current state.
10. What () process promises to reset this running command at work execution time. This process receives two parameters: job and what.
PROCEDURE
(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.
Third, check the relevant job information
1. Related views
Dba_jobs,all_jobs,user_jobs,dba_jobs_running (contains information about running job)
2. View related information
SELECT JOB, Next_date, next_sec, failures, broken from dba_jobs;
Job-related information that is running
SELECT SID, R.job, Log_user, R.this_date, r.this_sec
From Dba_jobs_running R, Dba_jobs J
WHERE r.job = j.job;
JOB QUEUE Lock Related information
SELECT SID, type, ID1, ID2 from v$lock WHERE TYPE = ' JQ ';
Iv. Simple Examples
A simple example (in the Sql/plus environment):
Create a test table

[C-sharp] View plaincopyprint?
CREATE TABLE TEST (a date);
CREATE TABLE TEST (a date);
Create a custom procedure

[C-sharp] View plaincopyprint?
CREATE OR REPLACE PROCEDURE MYPROC as
BEGIN
INSERT into TEST VALUES (sysdate);
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE MYPROC as
BEGIN
INSERT into TEST VALUES (sysdate);
COMMIT;
END;
/
Create Job

[C-sharp] View plaincopyprint?
sql> variable JOB1 number;
Sql>
BEGIN
Dbms_job. SUBMIT (: JOB1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes per day, or one minute to run the test procedure once
END;
/
sql> variable JOB1 number;
Sql>
BEGIN
Dbms_job. SUBMIT (: JOB1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes per day, or one minute to run the test procedure once
END;
/

Run Job

[C-sharp] View plaincopyprint?
Sql>
BEGIN
Dbms_job. RUN (: JOB1);
END;
/
Sql> SELECT to_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;
Sql>
BEGIN
Dbms_job. RUN (: JOB1);
END;
/
Sql> SELECT to_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;

Delete Job

[C-sharp] View plaincopyprint?
Sql>
BEGIN
Dbms_job. REMOVE (: JOB1);
END;
/
Sql>
BEGIN
Dbms_job. REMOVE (: JOB1);
END;
/
V. Summary
About Job run time
1: Execute Every Minute
Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)
2: Regular execution every day
Example: Daily 1 o'clock in the morning execution
Interval = TRUNC (sysdate) + 1 +1/(24)
3: Regular Weekly execution
For example: Every Monday 1 o'clock in the morning execution
Interval = TRUNC (Next_day (sysdate, ' Monday ')) +1/24
4: Regular Monthly execution
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24
5: 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
6: 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
7: 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
Operating frequency settings for the job
1. Daily fixed time operation, such as morning 8:10 minutes: Trunc (sysdate+1) + (8*60+10)/24*60
Available in 2.Toad:
Daily: Trunc (sysdate+1)
Weekly: Trunc (sysdate+7)
Per month: trunc (sysdate+30)
Each Sunday: Next_day (trunc (sysdate), ' SUNDAY ')
6 points per day: trunc (sysdate+1) +6/24
Half an hour: sysdate+30/1440
3. Run the 15th minute of each hour, for example: 8:15,9:15,10:15...:trunc (sysdate, ' hh ') +75/1440.
Why is the job not running?
1. First to understand the job parameter description: 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 greater than this value, there will be queued, the minimum is 0, indicating that the job is not running, The maximum value is 36, 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.
2. Diagnosis: First determine whether the above two parameters are set correctly, especially the first parameter, set to 0, all jobs will not run, confirm the error, we continue down.
3. Use the following SQL to view the job's broken,last_date and Next_date,last_date is the end time of the most recent job run, Next_date is the next execution time based on the frequency of the setup, 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 to see his last_date has changed, the job can run normally, modify the broken status of SQL as follows:
Declare
BEGIN
Dbms_job. Broken (<job_id>,false);
END;
4. Use the following SQL query to see if 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 look at the v$access and v$locked_object two view, if other processes are found to lock the job-related object, including pkg/function/ Procedure/table and other resources, then it is necessary to remove the other processes, the need to remove the job process, and then rerun to see the results.
5. What if the above is normal, but the job is not run? 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--shuts down the job process and waits for 5--10 seconds
Alter system set JOB_QUENE_PROCESSES=5--Restores the original value
Bug in 6.Oracle
Oracle9i There is a bug, when the counter to 497 days, just reach its maximum, then the count will become-1, continue to count becomes 0, and then the counter will no longer run. If this is the case, we have to restart the database, we have a production version of the database is 9205, there has been such a problem, and then the user about the time after the restart is no problem. However, other Oracle7345 and oracle8i databases did not find this problem.
7. The database check basically this much, if the job run still have a problem, that need to cooperate with the user to see whether the program itself is a problem, such as the amount of data processing, or slow network speed, resulting in the operation is too long, it needs specific circumstances specific analysis. We can do the job manually through the following SQL to see:
Declare
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.
Reprint to http://blog.csdn.net/nsj820/article/details/5643421

About Oracle Dbms_job timed execution of content.