Oracle Job syntax and time interval settings ____oracle

－－－－－－－－－－－－－－－－－－－－－－－－－－

Initialize related parameters job_queue_processes
Alter system set JOB_QUEUE_PROCESSES=39 scope=spfile;//maximum value cannot exceed 1000 job_queue_interval = 10//Schedule Job Refresh frequency seconds

Job_queue_process represents the number of jobs that Oracle can concurrently work with by statement

Show parameter job_queue_process;

To view the values of job_queue_process in Oracle. When the job_queue_process value of 0 indicates that all Oracle jobs are stopped, you can use the statement

ALTER SYSTEM SET job_queue_processes = 10;

To adjust the job that started Oracle.

Related views:
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running job

－－－－－－－－－－－－－－－－－－－－－－－－－

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 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 Job
Sql> begin
Dbms_job.remove (: JOB1);
End
/

Dba_jobs
===========================================
field (column) type description
Unique identifier of the job number task
Log_user VARCHAR2 (30) the user who submitted the task
Priv_user VARCHAR2 (30) Users assigned permissions to the task
Schema_user VARCHAR2 (30) User mode for parsing a task
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) such as HH24:MM:SS format last_date date hours, minutes, and seconds
This_date date is running the start time of the task, or null if the task is not running
This_sec VARCHAR2 (8) such as HH24:MM:SS format this_date date hours, minutes, and seconds
Next_date date the next time the task is scheduled to run
Next_sec VARCHAR2 (8) such as HH24:MM:SS format next_date date hours, minutes, and seconds
Total_time number The total time required for the task to run, in seconds
Broken VARCHAR2 (1) flag parameter, y indicates that the task is interrupted and will not run later
INTERVAL VARCHAR2 (200) An expression used to calculate the next run time
Failures number task runs continuously without success
WHAT VARCHAR2 (2000) Pl/sql block to perform a task
Current_session_label RAW MLSLABEL Trusted Oracle Session character for this task
Clearance_hi RAW MLSLABEL Oracle maximum gap that the task can trust
Clearance_lo RAW MLSLABEL Oracle minimum gap that the task can trust
Nls_env VARCHAR2 (2000) The NLS session settings that the task runs
Some other session parameters that the Misc_env RAW (32) task runs

－－－－－－－－－－－－－－－－－－－－－－－－－－
Describe interval parameter values
Every night 12 o'clock ' TRUNC (sysdate + 1) '
Daily 8:30 A.M. ' TRUNC (sysdate + 1) + (8*60+30)/(24*60)
Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
12 O'Clock ' TRUNC (Last_day (sysdate) + 1) ' At midnight on the first day of the month
Each quarter on the last day of the evening 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and 6:10 A.M. ' TRUNC (Next_day (sysdate, ' SATURDAY '), Next_day (Sysdate, "SUNDAY"))) + (6X60+10)/(24x60) '

－－－－－－－－－－－－－－－－－－－－－－－－－－

1: Per minute execution

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

2: Regular daily execution

Example: Daily 1 o'clock in the morning execution

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

3: Regular Weekly execution

For example: Every Monday 1 o'clock in the morning execution

Interval => TRUNC (Next_day (sysdate, ' Monday ')) +1/24

4: Regular Monthly execution

Example: Monthly 1st 1 o'clock in the morning

Interval =>trunc (Last_day (sysdate)) +1+1/24

5: Quarterly Regular execution

For example, the first day of every quarter, 1 o'clock in the morning execution

Interval => TRUNC (add_months (sysdate,3), ' Q ') + 1/24

6: Every six months regular execution

For example: July 1 and January 1 every year 1 o'clock in the morning

Interval => add_months (trunc (sysdate, ' yyyy '), 6) +1/24

7: Regular Annual execution

For example: January 1 every year 1 o'clock in the morning execution

Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24