Oracle Timer (JOB) Rollup

for DBA , the database Job again familiar, because often to the database automatically execute some scripts, or do database backup, or do data extraction, or do database performance optimization, including rebuilding indexes and so on. However,the processing ofOracle timer Job time, the ever- changing, today I put more commonly used to summarize the following:

Before summing up, the first Job the parameter one by one illustrates:

Job parameter is determined by the Submit () Process returns the Binary_ineger . This value is used to uniquely identify a job;

What parameter is the one that will be executed PL/SQL code block;

next_date The parameter indicates when the job will run. You can not specify this value when writing a Job;

interval parameters When this work will be re-executed.

which Interval This value is determined Job when, the key to being re-executed.

For example, there are stored procedures p_dosomethings , which needs to be performed at different intervals.

 Declare
 jobno number;
 begin
 Dbms_job.submit (
 Jobno,
     'p_dosomething;',  --What
 To_date ('20090101020000','YYYY-MM-DD Hh24:mi:ss'),--next_date, can not fill
     'interval Time string'--interval, key settings
   );
commit;

End

Create a job that executes functions with no parameters at timed intervals:

DECLARE

Job Binary_integer; --Task ID

V_begin DATE; --Start time

V_interval VARCHAR2 (50);--time interval begin v_begin: = TRUNC (sysdate) + 12/24; --< 12:00> V_interval of the current date: = ' sysdate +1 ';--< executes every 24 hours > Dbms_job.submit (JOB,--job number Sg_wf_zxcf_syn; ',--what value V_begin,--Next Date V_interval,--Interval FALSE, 0,--Total time FALSE);  Dbms_output.put_line (' job ' number: ' | | job '); COMMIT; END;

1. Execute every Minute

Interval = TRUNC (sysdate, 'mi') + 1 /( )

2. Regular execution every day

For example: Every morning. 2 Point Execution

Interval = TRUNC (sysdate) + 1  + 2 /(per)

3. regular Weekly Execution

For example: Every Monday morning 2 Point Execution

Interval = TRUNC (Next_day (sysdate,2)) + 2 / - - Monday, the second day of the week

4. Regular Monthly Execution

For example: Monthly 1 early in the day 2 Point Execution

Interval = TRUNC (Last_day (sysdate)) + 1 + 2 / -

5. Quarterly Scheduled execution

For example, the first morning of every quarter 2 Point Execution

Interval = TRUNC (add_months (sysdate,3),' Q ') + 2 / -

6. every half-yearly scheduled execution

For example: Every year 7 Month 1 Day and 1 Month 1 early in the day 2 Point

Interval = add_months (trunc (sysdate,' yyyy '),6) + 2/

7. scheduled execution every year

For example: Every year 1 Month 1 early in the day 2 Point Execution

Interval = add_months (trunc (sysdate,' yyyy '),+ 2 / -

8. SQL statements

-------------Query Job-----------------
Select Job, what, Next_date, Next_sec, sysdate, failures, Broken,interval
From User_jobs A;

-------------Add Job-----------------
variable n number;

Begin
Dbms_job.submit (: N, ' My_jop; ', Sysdate, ' sysdate+2/(24*60*60) ');
Commit
End

-------------Modify the Job-----------------
Begin
Dbms_job.change ("MY_JOP; ', Sysdate, ' sysdate+2/(24*60) ');
Commit
End

Begin
Dbms_job.change (, ' My_jop; ', To_date (' 2011-08-01 22:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' sysdate+1 ');
Commit
End

-------------Delete Job-----------------
Begin
Dbms_job.remove (41);
End

-------------Run the job-----------------------
Begin
Dbms_job.run (41);
End

9. Job Case:

Build a job, timedto execute a function with parameters:DECLARE Job Binary_integer;--Task ID v_begin DATE; --Start time V_interval VARCHAR2 (50);--time interval begin v_begin: = TRUNC (sysdate) + 12/24;--< 12:00> V_interval of the current date: = ' sysdate +1 ';--< executes > Dbms_job.submit every 24 hours (JOB,--job number ' Declare v_sumsg varchar (200); V_SUMWF varchar (200); V_SUMZXCF varchar (200); Begin Sg_wf_zxcf_syn (V_SUMSG,V_SUMWF,V_SUMZXCF); end; ',--what value V_begin,--Next Date V_interval,--Interval FALSE, 0,--Total time FALSE);  Dbms_output.put_line (' job ' number: ' | | job '); COMMIT; END;

Oracle Timer (JOB) Rollup