Oracle uses jobs for periodic and scheduled task execution

In oracle, jobs are used to periodically execute Task 1. view the jobselect job in the oracle database, what from user_jobs; 2. delete jobexecute dbms_job.remove (jobID) in oracle. Note: jobID is the job value queried in "select job, what from user_jobs. III. create a job, that is, DBMS_Jobs www.2cto.com 1. the following is an example: SQL code SQL> create table a (a date ); table created // create a Process procedure SQL> create or replace procedure test as 2 begin 3 insert into a values (sysdate); 4 end; 5/Procedure created // submit job SQL> declare 2 job1 number; // define a numeric variable 3 begin 4 dbms_job.submit (job1, 'test; ', sysdate, 'sysdate + 100'); // The value is 1/1440 minutes per day. This statement indicates that execution starts from the current time and is executed every minute. sysdate indicates the current time of the system. Www.2cto.com 5 end; 6/PL/SQL procedure successfully completed job1 --------- 4 SQL> commit; Commit complete/run job SQL> begin 2 dbms_job.run (4); 3 end; 4/PL/SQL procedure successfully completed // Delete job SQL> begin 2 dbms_job.remove (4); 3 end; 4/PL/SQL procedure successfully completed SQL> commit; commit complete // job change // modify a job execute dbms_job.change (186, null, null, 'sysdate + 3'); execute dbm S_job.change (186, 'Scott. test (update) '); 2. the second example is given below: www.2cto.com SQL code create or replace procedure backup_table as v_year varchar2 (20); begin select to_char (sysdate, 'yyyy-mm') into v_year from dual; insert into t_backup select * from t_table where to_char (C_WORKTIME, 'yyyy-mm') = v_year; commit; exception when others then rollback; end;/declare JOB_BACKUP number; begin dbms_job.submit (JOB_BACKUP, 'Backup _ table; ', to_date ('01-07-2012 02:00:00', 'dd-mm-yyyy hh24: mi: ss '), 'Add _ MONTHS (trunc (sysdate, ''yyyy'), 6) + 100'); commit; end;/Description: A Stored Procedure of backup_table is created first, this stored procedure backs up the c_worktime field in t_table as the data of the current month to the t_backup table. Then, a JOB_BACKUP is created, and the first execution time of the JOB is 02:00:00, the next execution time will be performed every six months. 4. the scheduled execution and interval of a job refer to the interval from the last execution to the next execution. When interval is set to null, is deleted from the queue. If we need to periodically execute the job, we need to use 'sysdate + M. 1. execute Interval => TRUNC (sysdate, 'mi') + 1/(24*60) 2. daily scheduled execution example: Execute Interval => TRUNC (sysdate) + 1 + 1/(24) 3 at every day. periodical execution every week for example: Execute Interval => TRUNC (next_day (sysdate, 'monday') + 1/244 at every Monday. scheduled monthly execution example: Execute Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/245 at on the first day of each month. perform scheduled execution on a quarterly basis. For example, execute Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/246 at on the first day of each quarter. scheduled execution every six months, for example, at Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/247. scheduled execution every year for example: Execute Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + January 1 at on January 1, 1/24