Oracle job scheduled task usage details

oracle Medium job What I can do for you is to execute stored procedures in your specified time format , Perform a task regularly . Here's a small case , Every time 15 Minutes to insert a piece of data into a table

One

  1. Create a test table

-- Create table
create table A8
(
  a1 VARCHAR2(500)
)
tablespace DSP_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

    2. Create stored procedure Insert data into the test table

create or replace procedure proc_add_test as
begin
  insert into a8 values (to_char(sysdate, 'yyyy-mm-dd hh:mi'));/* Insert data into test table */
  commit;
end;

 3. establish job Timing task   Implement automatic calling of stored procedures ( current time 17:03)

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /* Automatic generation JOB_ID*/  
        WHAT => 'proc_add_test;',  /* The name of the stored procedure to execute or SQL sentence */  
        NEXT_DATE => sysdate+3/(24*60),  /* Initial execution time - next 3 minute */  
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /* every other 1 Once per minute */
      );  
  commit;
end;

 4. That is to say, we should start from 17:06 Start every other 1 The stored procedure is executed every 15 minutes   Here's the deadline 17:12 The data of the test table of points

  Two

1. You can query the system table to see the job Information

select * from user_jobs;

 2. Manual sql call job   ( Call directly job You can ignore the start time )

 begin
   DBMS_JOB.RUN(40); /*40 job Of id*/
 end;

3. Delete task

begin
  /* Delete automatically executed job*/
  dbms_job.remove(40);
end;

4. stop it job

dbms.broken(job,broken,nextdate);   
dbms_job.broken(v_job,true,next_date);        /* Stop one job, Inside parameters true But false,next_date（ Stop at some point ） But sysdate（ Stop at once ）.   */

5. Modification interval

dbms_job.interval(job,interval);

6. Modify next execution time

dbms_job.next_date(job,next_date);

7. Modify the action to be performed

dbms_job.what（jobno,'sp_fact_charge_code;'）;  -- Modify a job name  

3、 ... and Other intellectual

1. save job Tables of information user_jobs Main field description

2.INTERVAL Examples of common values of parameters

1. At midnight every day 12 spot             ''TRUNC(SYSDATE + 1)''     
2. Every day in the morning 8 spot 30 branch          ''TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)''     
3. Every Tuesday at noon 12 spot          ''NEXT_DAY(TRUNC(SYSDATE ), ''''TUESDAY'''' ) + 12/24''     
4. Midnight on the first day of every month 12 spot     ''TRUNC(LAST_DAY(SYSDATE ) + 1)''     
5. On the last night of every quarter 11 spot  ''TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ''Q'' ) -1/24''     
6. Every Saturday and Sunday morning 6 spot 10 branch     ''TRUNC(LEAST(NEXT_DAY(SYSDATE, ''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）''    
7. Every time 3 Once per second              'sysdate+3/(24*60*60)'   
8. Every time 2 Once per minute            'sysdate+2/(24*60)'   
9.   
10. 1: Every minute   
11. Interval => TRUNC(sysdate,'mi') + 1/ (24*60) -- Every minute   
12. interval => 'sysdate+1/（24*60）'  -- Every minute   
13. interval => 'sysdate+1'    -- Every day   
14. interval => 'sysdate+1/24'   -- Every hour   
15. interval => 'sysdate+2/24*60' -- Every time 2 minute   
16. interval => 'sysdate+30/24*60*60'  -- Every time 30 second   
17. 2: Regular execution every day   
18. Interval => TRUNC(sysdate+1)  -- Every morning 0 Point to perform   
19. Interval => TRUNC(sysdate+1)+1/24  -- Every morning 1 Point to perform   
20. Interval => TRUNC(SYSDATE+1)+(8*60+30)/(24*60)  -- Every day in the morning 8 spot 30 Points to perform   
21. 3: Weekly scheduled execution   
22. Interval => TRUNC(next_day(sysdate,' Monday '))+1/24  -- Every Monday morning 1 Point to perform   
23. Interval => TRUNC(next_day(sysdate,1))+2/24  -- Every Monday morning 2 Point to perform   
24. 4: Regular monthly execution   
25. Interval =>TTRUNC(LAST_DAY(SYSDATE)+1)  -- monthly 1 Early morning 0 Point to perform   
26. Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24  -- monthly 1 Early morning 1 Point to perform   
27. 5: Regular implementation every quarter   
28. Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q')  -- In the early hours of the first day of each quarter 0 Point to perform   
29. Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q') + 1/24  -- In the early hours of the first day of each quarter 1 Point to perform   
30. Interval => TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24  -- The night of the last day of each quarter 11 Point to perform   
31. 6: Regular implementation every half a year   
32. Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24  -- Every year, 7 month 1 Day and 1 month 1 Early morning 1 spot   
33. 7: Regular execution every year   
34. Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24  -- Every year, 1 month 1 Early morning 1 Point to perform  

Reproduced in Oracle Job Detailed explanation of the use of scheduled tasks - Chestnuts - Blog Garden