Use of OracleJob (scheduled execution), oraclejob execution

Use of OracleJob (scheduled execution), oraclejob execution

When using OracleJob (scheduled execution), what jobs in oracle can do for you is to execute the stored procedure in the specified time format and regularly execute a task. The following is a small case where a data entry is inserted to a table every 15 minutes.

I

1. Create a test table

-- Create tablecreate 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 a stored procedure to insert data to the test table

Create or replace procedure proc_add_test asbegin insert into a8 values (to_char (sysdate, 'yyyy-mm-dd hh: mi ');/* insert data to the test table */commit; end;

3. Create a job scheduled task to automatically call the Stored Procedure (current time)

Declare job number; BEGIN DBMS_JOB.SUBMIT (JOB => job,/* automatically generate JOB_ID */WHAT => 'proc _ add_test ;', /* Name of the stored procedure to be executed or SQL statement */NEXT_DATE => sysdate + 3/(24*60 ), /* initial execution time-next 3 minutes */INTERVAL => 'trunc (sysdate, ''mi'') + 1/(24*60) '/* execute once every 1 minute */); commit; end;

4. That is, the stored procedure should be executed every 1 minute starting from. The following is the data of the test table as.

II

1. You can view the job information in the query system table.

select * from user_jobs；

2. Manually call a job by SQL (directly calling a job can ignore the start time)

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

3. delete a task

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

4. Stop a job

Dbms. broken (job, broken, nextdate); dbms_job.broken (v_job, true, next_date);/* stop a job. The true parameter in the job is also false, next_date (stop at a certain time) or sysdate (stop immediately ). */

5. Modify the interval

dbms_job.interval(job，interval);

6. Modify the next execution time

dbms_job.next_date(job，next_date);

7. Modify the operation to be executed

Dbms_job.what (jobno, 'SP _ fact_charge_code; '); -- modify a job name

3. Other knowledge

1. Description of main fields in the user_jobs table storing job information

Column name data type description job number the unique identifier of a JOB: LOG_USER VARCHAR2 (30) submitter user PRIV_USER VARCHAR2 (30) User SCHEMA_USER VARCHAR2 (30) user Mode for job syntax analysis LAST_DATE DATE the last time the job was successfully run LAST_SEC VARCHAR2 (8) for example, HH24: MM: SS format last_date hour, minute and second THIS_DATE start time of the task being run. If no task is run, it is the hour of nullTHIS_SEC VARCHAR2 (8) such as HH24: MM: SS format this_date, minutes and seconds NEXT_DATE DATE the next scheduled run of the task NEXT_SEC VARCHAR2 (8) such as HH24: MM: SS format next_date hour, minute and second TOTAL_TIME NUMBER the total time required for the task to run. Unit: Second BROKEN VARCHAR2 (1) Flag parameter. Y indicates that the task is interrupted. INTERVAL VARCHAR2 (200) will not be run in the future) the expression used to calculate the next running time. failures number, the NUMBER of consecutive unsuccessful running times of the task. WHAT VARCHAR2 (2000) PL/SQL block for executing the task

2. Example of common values of INTERVAL Parameters

''Trunc (SYSDATE + 1) ''at midnight every day 08:30 ''trunc (SYSDATE + 1) + (8*60 + 30)/(24*60) '''next _ DAY (TRUNC (SYSDATE), ''' tuesday '''') + 12/24 '''midnight on the first day of each month ''' TRUNC (LAST_DAY (SYSDATE) + 1) '''trunc (ADD_MONTHS (SYSDATE + 2/24, 3), ''q '') -1/24 ''TRUNC (LEAST (NEXT_DAY (SYSDATE, '''saturday"), NEXT_DAY (SYSDATE, "SUNDAY "))) + (6*60 + 10)/(24*60) ''run 'sysdate + 3/(24*60*60) 'every 3 seconds) 'sysdate + 2/(24*60 )'

1: executed per minute

Interval => TRUNC (sysdate, 'mi') + 1/(24*60) -- execute interval every minute => 'sysdate + 1/(24*60) '-- execute interval => 'sysdate + 1' every minute -- interval => 'sysdate + 000000' every day -- interval => 'sysdate + 1/24 * 60' every two minutes => 'sysdate + 30/24*60 * 60' -- every 30 seconds

2: daily scheduled execution

Interval => TRUNC (sysdate + 1) -- execute Interval => TRUNC (sysdate + 1) At every day) + 1/24 -- execute Interval => TRUNC (SYSDATE + 1) + (8*60 + 30)/(24*60) at 08:30 every morning

3: scheduled weekly execution

Interval => TRUNC (next_day (sysdate, 'monday') + 1/24 -- execute Interval at every Monday => TRUNC (next_day (sysdate, 1 )) + 2/24 -- run at every Monday

4: scheduled monthly execution

Interval => TTRUNC (LAST_DAY (SYSDATE) + 1) -- execute Interval at on the first day of each month => TRUNC (LAST_DAY (SYSDATE )) + 1 + 1/24 -- executed at on the first day of each month

5: Periodical execution on a quarterly basis

Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') -- execute Interval at on the first day of each quarter => TRUNC (ADD_MONTHS (SYSDATE, 3 ), 'q') + 1/24 -- execute Interval => TRUNC (ADD_MONTHS (SYSDATE + 2/24, 3), 'q' at on the first day of each quarter ') -1/24 -- run at on the last day of each quarter

6: scheduled execution every six months

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24 -- each year on January 1, July 1 and January 1, January 1

7: scheduled execution every year

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24 -- executed at on January 1, January 1 every year