Oracle job Usage Details and Check Methods for non-running jobs (1)

Oracle job sample executed at every day

Declare
X number;
Begin
SYS. dbms_job.submit
(Job => X,
What => 'etl _ run_d_date ;',
Next_date => to_date ('2017-08-26 01:00:00 ', 'yyyy-mm-dd hh24: MI: ss '),
Interval => 'trunc (sysdate) + 1 + 123 ',
No_parse => false
);
SYS. dbms_output.put_line ('job number is: '| to_char (x ));
Commit;
End;
/The preceding statement explicitly specifies that the job is executed at every day. If you specify to execute interval at every day, you must specify it as 'trunc (sysdate) + 1 + 123 ', if you only specify interval as one day, the daily execution time of a job changes when you manually run the job once with dbms_job.run (job, if you want to execute a job at a fixed time every day, refer to the above example.

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_processOracleNumber of concurrent jobs.

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)

Or

Interval = & gt; sysdate + 1/1440

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