Oracle job syntax and Interval Settings

Initialize related parameters job_queue_processes

Alter system set job_queue_processes = 39 scope = spfile; // The maximum value cannot exceed 1000; job_queue_interval = 10 // The refresh frequency of the scheduled job is measured in seconds.

Job_queue_process indicates the number of jobs that can be concurrently run by Oracle.

Show parameter job_queue_process;

To view the value of job_queue_process in Oracle. When job_queue_process is set to 0, all Oracle jobs are stopped.

Alter system set job_queue_processes = 10;

To adjust the Oracle startup job.

Related views:

Dba_jobs

All_jobs

User_jobs

Dba_jobs_running contains information about running jobs.

 

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

Submit job Syntax:

Begin

SYS. dbms_job.submit (job =>: Job,

What => 'P _ clear_packbal ;',

Next_date => to_date ('04-08-2008 05:44:09 ', 'dd-mm-yyyy hh24: MI: ss '),

Interval => 'sysdate + 1/360 ');

Commit;

End;

/

 

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

Create a job

Variable jobno number;

Begin

Dbms_job.submit (: jobno, 'P _ cred_plan; ', sysdate, 'sysdate + 1/2880', true );

Commit;

Run job

SQL> begin

Dbms_job.run (: job1 );

End;

/

Delete a job

SQL> begin

Dbms_job.remove (: job1 );

End;

/

Dba_jobs

========================================================== ===

Field (column) type description

Unique ID of a job number

Log_user varchar2 (30) user who submits the task

Priv_user varchar2 (30) user with task Permissions

Schema_user varchar2 (30) User Mode for Syntactic Analysis of tasks

Last_date date the time when the last task was successfully run

Last_sec varchar2 (8) such as hh24: mm: hour, minute, and second of the SS format last_date

The start time of the task being run by this_date. If no task is running, the value is null.

This_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the this_date date in SS format

Next_date date the next scheduled task running time

Next_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the SS format next_date

Total_time number the total time required to run the task, in seconds

The broken varchar2 (1) Flag parameter. y indicates that the task is interrupted and will not run later.

Interval varchar2 (200) is the expression used to calculate the next line time.

Failures number the number of times the task failed to run continuously

What varchar2 (2000) PL/SQL block for task execution

Current_session_label raw mlslabel trust Oracle session character of the task

Clearance_hi raw mlslabel maximum gap between Oracle databases trusted by this task

Clearance_lo raw mlslabel minimum Oracle gap trusted by this task

NLS session settings for running the nls_env varchar2 (2000) task

Other session parameters of the misc_env raw (32) task

 

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

Parameter Value of Interval

'Trunc (sysdate + 1) 'At midnight every day )'

Every morning 08:30 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'

Next _ day (trunc (sysdate), ''tuesday'') + 12/24 at every Tuesday'

'Trunc (last_day (sysdate) + 1) 'at midnight on the first day of each month )'

'Trunc (add_months (sysdate + 2/24, 3), 'q')-100' at on the last day of each quarter'

'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6 × 60 + 10) /(24 × 60 )'

 

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

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