Detailed description of Oraclejob usage and Check Methods for non-running jobs

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

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

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_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)

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

To check whether a JOB is not running, follow these steps:

ORACLE has a regular scheduling mechanism, which is managed using the dbms_job package.

The JOB is not running, and it is depressing,

It is best to execute this command to get exec dbms_ijob.set_enabled (true );

The following provides a checklist to check the causes of job exceptions:

1) Instance in restricted sessions mode?

Check if the instance is in restricted sessions mode:

Select instance_name, logins from v $ instance;

If logins = RESTRICTED, then:

Alter system disable restricted session;

^-Checked!

2) JOB_QUEUE_PROCESSES = 0

Make sure that job_queue_processes is> 0

Show parameter job_queue_processes

^-Checked!

3) _ SYSTEM_TRIG_ENABLED = FALSE

Check if _ system_enabled_trigger = false

Col parameter format a25

Col value format a15

Select a. ksppinm parameter, B. ksppstvl value from x $ ksppi a, x $ ksppcv B

Where a. indx = B. indx and ksppinm = '_ system_trig_enabled ';

If _ system_trig_enabled = false, then

Alter system set "_ system_trig_enabled" = TRUE scope = both;

^-Checked!

4) Is the job BROKEN?

Select job, broken from dba_jobs where job = ;

If broken, then check the alert log and trace files to diagnose the issue.

^-Checked! The job is not broken.

5) Is the job COMMITted?

Make sure a commit is issued after submitting the job:

BEGIN

SYS. DBMS_JOB.SUBMIT

(

Job => X

, What => 'dbms _ utility. analyze_schema

("SCOTT", "COMPUTE", NULL );'

, Next_date => to_date ('2016/08/06 09:35:00 ', 'dd/mm/yyyy hh24: mi: ss ')

, No_parse => FALSE

);

COMMIT;

END;

/

If the job executes fine if forced (I. e., exec dbms_jobs.run ( );), Then likely a commit

Is missing.

^-Checked! The job is committed after submission.

6) UPTIME & gt; 497 days

Check if the server (machine) has been up for more than 497 days:

For SUN, use 'uptime' OS command.

If uptime> 497 and the jobs do not execute automatically, then you are hitting unpublished bug 3427424

(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

^-Checked! The server in this case has been up 126 days only

7) DBA_JOBS_RUNNING

Check dba_jobs_running to see if the job is still running:

Select * from dba_jobs_running;

^-Checked! The job is not running.

LAST_DATE and NEXT_DATE

Check if the last_date and next_date for the job are proper:

Select Job, Next_date, Last_date from dba_jobs where job = ;

^-NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

9) NEXT_DATE and INTERVAL

Check if the Next_date is changing properly as per the interval set in dba_jobs:

Select Job, Interval, Next_date, Last_date from dba_jobs where job = ;

^-This is not possible since the job never gets executed automatically.

10) Toggle value for JOB_QUEUE_PROCESSES

Stop and restart CJQ process (es)

Alter system set job_queue_processes = 0;

-

Alter system set job_queue_processes = 4;

Ref: Bug 2649244 (fixed by: 9015,920 3, 10201)

^-Done but did not help

11) DBMS_IJOB (Non-receivented ):

Last ditch effort.

Either restart the database or try the following:

Exec dbms_ijob.set_enabled (true );

Ref: Bug 3505718 (Closed, Not a Bug)

Done but did not help

These are the most common causes for this behavior.

Solution

The solution ended up to be the server (machine) uptime.

Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.

To implement the solution, please execute the following steps:

1. Shutdown all applications, including databases.

2. Shutdown the server (machine)

3. Restart all applications, including databases.

4. Check that jobs are executing automatically.

From metalink docs: 313102.1