Oracle timer and oracle scheduled task

Oracle timer and oracle scheduled task

Declare

Jobno number;

Begin dbms_job.submit (

Jobno, -- timer ID, automatically obtained by the System

'Prc _ INSERT; ', -- what is the name of the execution process

Sysdate, -- next_date, the time when the timer starts to execute, so that write indicates immediate execution

'Sysdate + 100' -- interval, set the timer execution frequency, so that the write is executed every 15 minutes.

);

Commit;

End;

 

Here, the first parameter is the task number, which is automatically assigned a value. You can also use isubmit to manually specify

The second parameter is the task process to be executed. If the code is long, you can write it into a stored procedure and put it in it for calling, such as 'Pro _ test; '(pro_test is assumed to be a stored procedure name)

The third parameter is the time when the automatic task is executed for the first time. If you want it to be executed immediately, use sysdate

For the last parameter, the system specifies the next execution time based on the value of this parameter.

 

 

Declare

Jobno

Number;

Begin

Dbms_job.remove (45 );

Commit;

End;

 

Exec dbms_job.remove (83); -- deletes a timer.
Exec dbms_job.run (84); -- run a timer
Exec DBMS_JOB.BROKEN (83, SYS. DIUTIL. INT_TO_BOOL (1); -- stop a timer
Exec DBMS_JOB.INTERVAL (84, 'sysdate + 100'); -- change the execution frequency of a timer to execute every hour.

Select * from user_jobs; -- view the scheduling task

Select * from dba_jobs_running; -- view the ongoing scheduling task

Select * from dba_jobs; -- view the completed scheduling task

 

 

Description of timer parameters:

The myjob parameter is the binary_ineger returned by the Submit () process. This value uniquely identifies a job;

What parameter is the PL/SQL code block to be executed. Here it refers to a stored procedure. Pay attention to the semicolon following the name;

The next_date parameter specifies when the job will be run. You can leave this value unspecified when writing a Job;

The interval parameter indicates when the job will be re-executed.

For more information about interval Settings, see the following examples:

1. Execution per minute

Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

2. daily scheduled execution

Example: Execute at every day

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

3. weekly scheduled execution

Example: Execute at every Monday

Interval => TRUNC (next_day (sysdate, 2) + 2/24 -- Monday, the second day of a week

4. scheduled monthly execution

For example, the task is executed at on the first day of every month.

Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 2/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') + 2/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) + 2/24

7. Regular annual execution

For example, it is executed at on January 1, January 1 every year.

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 2/24