Oracle Job Knowledge __oracle

The most recent need is to use the Oracle Job, the past is now unfamiliar, or ready to tidy up the knowledge to stay, the following is a backup SMS Log Job processing process.

Part One: simply create a task

1. Create a stored procedure
Create or replace procedure Proc_backup_sendmessagelog is
Backuptime Date: =sysdate;
Begin
* NEW * *
Insert into Td_sendmessagelog_backup (id,managerid,mobilephone,touserid,content,sendtime,status,td_conference_id, Channelid,hassendlog,mobilegroup,backuptime)
Select Id,managerid,mobilephone,touserid,content,sendtime,status,td_conference_id,channelid,hassendlog, Mobilegroup,backuptime from Td_sendmessagelog
where Sendtime < add_months (sysdate,-1);

/* Delete operation * *
Delete from Td_sendmessagelog t
where T.sendtime < add_months (sysdate,-1);

Commit

/* Exception rollback */
exception
When others then
Rollback
Return

End Proc_backup_sendmessagelog;? 1?3

2. Create Job
declare job_backup_sendmessagelog number;
Begin
Sys.dbms_job.submit (Job => Job_backup_sendmessagelog,
What => ' Proc_backup_sendmessagelog; ',
Next_date => trunc (sysdate) +1,
Interval => ' add_months (trunc (sysdate), 1) ',
No_parse => true);
Sys.dbms_output.put_line (' Job number is: ' | | to_char (job_backup_sendmessagelog));
Commit
End;? 1?3

3. Query Job
SELECT * from User_jobs;
SELECT * from Dba_jobs_running;
SELECT * from Dba_jobs;
SELECT * from All_jobs;? 1?3

4. Delete Job
Begin
Sys.dbms_job.remove (491);
End;? 1?3

Part II: Job More information detailed

First, set 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;
　　
Ii. Introduction to the usage of Dbms_job package
Contains the following child procedures:
　　
Broken () process.
The change () procedure.
Interval () process.
Isubmit () process.
Next_date () process.
The Remove () procedure.
The Run () procedure.
Submit () process.
User_export () process.
What () process.
　　
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 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 the job fails for some reason, Oracle will retry the execution 16 times, and it will be marked as a job with a broken reboot status of broken, 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 by the Jobno when submitting the submit process
sql>end;
sql>/
B, use Dbms_job.broken () to mark broken again 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.
The What parameter is a block of Pl/sql code that runs from this work.
The Next_date parameter indicates when this work will be performed.
The interval parameter indicates how often a work is executed. China Network Management Forum Bbs.bitsCN.com
　　
3. The Interval () procedure is used to explicitly set the number of time intervals between the redo 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 how often a work is executed.
　　
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 () procedure is that the job parameter is passed as an in parameter and includes a work number provided by the developer. If the supplied work 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 and Next_date.
　　
PROCEDURE next_date (Job in Binary_ineger,
Next_date in date) 54ne.com
Job identifies a work that already exists. The Next_date parameter indicates the date and time this work should be performed.
　　
6. Remove () process to delete a scheduled operation. This procedure receives an argument:
　　
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 calling the Submit () procedure for this work. The work that is already running cannot be deleted by the calling program.
　　
7. The Run () process is used to perform a specified task immediately. This procedure receives only one parameter:
　　
PROCEDURE Run (Job in Binary_ineger)
　　
The job parameter identifies the work that will be executed immediately.
　　
8, the use of the submit () process, the work is normally planned well.
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.
The What parameter is the PL/SQL code block that will be executed. China Network Management Forum Bbs.bitsCN.com
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 at commit time or at execution--true indicates that the Pl/sql code should parse when it first executes, and False indicates that the PL/SQL code should be parsed 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 arranged work. The My_call parameter contains the body text required to resubmit this work in its current state.
　　
10. The What () process promises to reset this running command when work is performed. This process receives two parameters: job and what.
　　
PROCEDURE What (Job in Binary_ineger,
What in Out VARCHAR2)
The job parameter identifies a work that exists. The What parameter indicates the new PL/SQL code that will be executed.
　　

Third, view related job information
1. Related view
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running job


　　
2, view the relevant information
　　
Sql>select JOB, Next_date, next_sec, failures, broken
Sql>from Dba_jobs;
　　
JOB next_date next_sec Failures B
------- --------- -------- -------- -
9125 01-jun-01 00:00:00 4 N
14144 24-oct-01 16:35:35 0 N
9127 01-jun-01 00:00:00 Y
3 Rows selected.
　　
Running job-related information
　　
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;
　　
SID JOB log_user this_date this_sec
----- ---------- ------------- --------- --------
14144 HR 24-oct-94 17:21:24
8536 QS 24-oct-94 16:45:12
2 rows selected.
　　　
JOB QUEUE Lock Related information
　　
SELECT SID, TYPE, ID1, ID2
From V$lock
WHERE TYPE = ' JQ ';
　　
SID TY ID1 ID2
--------- -- --------- ---------
JQ 0 14144
1 row selected.
　　
Iv. Simple Examples
1, a simple example:

　　
To create a test table
Sql> CREATE TABLE TEST (a date);
　　
Table has been created.
　　
Create a custom procedure
sql> Create or replace procedure MYPROC as
2 begin
3 INSERT into TEST values (sysdate);
4 End;
5/
　　
Procedure has been created.
　　
Create Job
sql> variable JOB1 number;
Sql>
Sql> begin
2 Dbms_job.submit (: Job1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes a day, that is, a minute to run the test process once
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Run Job
Sql> begin
2 Dbms_job.run (: JOB1);
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Sql> Select To_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;
　　
Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
　　
Delete Job
Sql> begin
2 Dbms_job.remove (: JOB1);
3 END;
4/
The PL/SQL process has completed successfully.



2. One job example executed at 1 o ' Day:




DECLARE
X number;
BEGIN
SYS. Dbms_job. SUBMIT
(Job => X
, what => ' syn_rpt_members_relation; '
, Next_date => to_date (' 02-07-2008 01:00:00 ', ' dd/mm/yyyy hh24:mi:ss ')
, Interval => ' trunc (sysdate+1) + (1/24) '
, No_parse => FALSE
);
SYS. Dbms_output. Put_Line (' Job number is: ' | | to_char (x));
COMMIT;
End;
/
The above is explicitly specified 1 points per day to perform this job, if the designation is to be performed daily 12 o'clock noon interval need to be specified as ' trunc (sysdate) +1+12/24 ' If only designated interval for one day, so when you manually use Dbms_job.run ( Job to run once, the execution time of the job changes every day, and if you want the job to execute at a fixed time every day, refer to the example above.
Describes 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 '
12 O'Clock ' TRUNC (Last_day (sysdate) + 1) ' At midnight on the first day of the month
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) '



1: Per minute execution

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

Or

Interval => sysdate+1/1440

2: Regular daily execution

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

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

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

5: Quarterly Regular execution

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

Interval => TRUNC (add_months (sysdate,3), ' Q ') + 1/24

6: Every six months regular execution

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

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

7: 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
? 1?3
? 1?3