DBA_JOBS 2

[scohan]

A stored procedure job is submitted to dba_jobs but does not run - it just sits in dba_jobs. This procedure apparently worked last week. I don't see any errors for it in user_errors and it shows up as valid in user_source. The procedure should open a file, read in text, and insert the data into a table. It doesn't look like any sequences are involved.

This is someone else's code, but it was working in the past. Earlier this week I had to rebuild the db using an export file, but I don't know what's causing the job to hang.

Any suggestions on how to debug this??? thanks.

 

[sem]

Try to check job_queue_processes and job_queue_interval. You probably have no processes. You should also revise next_date parameter.

 

[scohan]

Are you talking about interval and next_date in dba_jobs? Where are job_queuee_processes and job_queue_interval? Thanks.

 

[karluk]

They are initialization parameters. Please read thread186-66500 for more details.

 

[sem]

I'm talking about next_date value in dba_jobs and itsexpression in dbms_jobs.submit.
job_queuee_processes and job_queue_interval are in init.ora file. You may also view them through v$parameter.

 

[scohan]

We're not using replication. Aren't job_queue_processes and job_queue_interval parameters used for replication? The next _date parameter is set initially. Thanks.

 

[sem]

No, they define the number of background processes, executing your jobs and the periodicity these proccesses scan job table. If you have no job_queue_processes, nobody scans the schedule, so nobody executes your jobs. If job_queue_interval is too high, you'll get your job running after the scheduled time and the nearest scan time.

 

[stevecal]

This is how it goes:

There are two parms in init.ora which control the job queue: JOB_QUEUE_PROCESSES (JQP), JOB_QUEUE_INTERVAL (JQI) and a third which has no known effect: JOB_QUEUE_KEEP_CONNECTIONS (JQKC).

JQP (or SNP) background processes kick off when the Oracle instance is initiated. There can be as many as specified in init.ora up to a max of 36 per Oracle instance. The loss of one of these is not mission critical (in fact there are lots of benefits since the fact that you can kill them means that processes that go AWOL can be removed this way - the processes will then be restarted clean by the Oracle instance).

Job queue processes 'periodically' do the checking of the job queue catalog to see if there are any tasks pending. The interval that this checking occurs at is determined by the value in JQI.

JQKC (obsolete under 8i) - no known effects. Leave this parameter alone.

 

[scohan]

Thanks sem and stevecal. There were multiple init.ora files in the dbs directory. The two that applied to this db were very similar, so similar that I thought they were identical. Guess where they weren't? The one I used had job_queue_processes = 0. The one I should have used had job_queue_processes = 1. Once I started with the correct init.ora file, the job was successfully executed. Thanks again. :=)