Practical Guide to Oracle job Automated tasks

As the scheduled task of oracle, oracle job is between the crontab at the server level and the application (such as the scheduled task of java spring). It has important value for data processing in the database layer.

Let's briefly share how oracle job calls stored procedures.

-- create a job without parameters

Variable etljobno number

Begin

Dbms_job.submit (: etljobno,'proc_ctitetl;',sysdate,'sysdate+3/1440')

End

/

-- create a job with parameters

Variable JOB_TEST number

Begin

Dbms_job.submit (: JOB_TEST,'begin hlqtestjob (1) end;',sysdate,'sysdate+1/1440')

Commit

End

/

-- start job

Begin

Dbms_job.run (: etljobno)

End

/

-- stop the job being executed

Begin

Dbms_job.broken (: etljobno,true)

End

/

-- Delete job

Begin

Dbms_job.remove (: etljobno)

End

Commit

/

-- query job

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS

If you want to interrupt already scheduledjob,dbms_job.broken (: etljobno,true), after the break, the BROKEN field of DBA_JOBS is Y, if you want to stop job or pause job

After modifying the stored procedure code, you can broken, and then start job again after modification.

With oracle job, you don't have to perform the necessary data processing at 3am every day, or choose to perform special tasks when the system load is low. Here are some oracle

Setting of time interval for job:

1: execute every minute

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

2: regular execution every day

For example: execution at 1: 00 a. M. every day

Interval = > TRUNC (sysdate) + 1 + 1 / (24)

3: regular execution every week

For example, it is executed at 1: 00 a.m. every Monday.

Interval = > TRUNC (next_day (sysdate,' Monday')) + 1 Unix 24

4: regular execution every month

For example: execution at 1: 00 a. M. on the 1st of every month

Interval = > TRUNC (LAST_DAY (SYSDATE)) + 1x 1max 24

5: regular execution every quarter

For example, it is executed at 1 am on the first day of every quarter.

Interval = > TRUNC (ADD_MONTHS (SYSDATE,3),'Q') + 1max 24

6: regular execution every half a year

For example: 1: 00 a.m. on July 1 and January 1 every year

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 6) + 1 Universe 24

7: regular execution every year

For example, it will be executed at 1: 00 a.m. on January 1 every year.

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 12) + 1 Universe 24