Oracle job automatic task Practical Guide

Oracle job automatic task Practical Guide

As a scheduled Oracle task, Oracle jobs are between crontab at the server level and applications (such as scheduled tasks of java spring). The database layer has important value for data processing.
The following is a brief introduction of how Oracle Jobs call stored procedures.

-- Create a job without Parameters
Variable etljobno number;
Begin
Dbms_job.submit (: etljobno, 'proc _ ctitetl; ', sysdate, 'sysdate + 100 ');
End;
/

-- Create a job with Parameters
Variable JOB_TEST number;
Begin
Dbms_job.submit (: JOB_TEST, 'in in hlqtestjob (1, ''aa''); end; ', sysdate, 'sysdate + 1/1440 ');
Commit;
End;
/

-- Start job
Begin
Dbms_job.run (: etljobno );
End;
/
-- Stop an ongoing job
Begin
Dbms_job.broken (: etljobno, true );
End;
/
-- Delete a job
Begin
Dbms_job.remove (: etljobno );
End;
Commit;
/

-- Query a job
Select job, NEXT_DATE, NEXT_SEC, FAILURES, broken from DBA_JOBS;
If you want to interrupt a scheduledjob, dbms_job.broken (: etljobno, true), after the interruption, the BROKEN field of DBA_JOBS is Y. If you want to stop a job or pause a job
Then, you can modify the stored procedure code and then start the job again.
With oracle job, you do not have to perform necessary data processing at every day, or select to execute special tasks when the system load is low. The following are some oracle
Job Interval Settings:
1: executed per minute
Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

2: daily scheduled execution
Example: Execute at every day
Interval => TRUNC (sysdate) + 1 + 1/(24)

3: scheduled weekly execution
Example: Execute at every Monday
Interval => TRUNC (next_day (sysdate, 'monday') + 1/24

4: scheduled monthly execution
Example: Execute at on the first day of every month
Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24

5: Periodical execution on a quarterly basis
For example, the statement is executed at on the first day of each quarter.
Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24

6: scheduled execution every six months
For example, at a.m. on January 1, July 1 and January 1, January 1
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24

7: scheduled execution every year
Example: Execute at on January 1, January 1 every year.
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------