Home » SQL & PL/SQL » SQL & PL/SQL » Facing a problem while using DBMS_JOB
Facing a problem while using DBMS_JOB [message #399636] Thu, 23 April 2009 04:15 Go to next message
manju.siddaramegowda
Messages: 3
Registered: July 2008
Location: Bangalore
Junior Member
Hi All,

I am facing a problem while using DBMS_JOB(using for the first time).

Here are the details. I am submitting 10 jobs using DBMS_JOB in a small procedure. The basic idea of doing this that I want all these 10 jobs to be run in parallel.

-----------------
PROCEDURE DRA_POPULATE_PRC IS
JOB_ID1 VARCHAR2(100);
JOB_ID2 number;
....
....
BEGIN
--1. Populating REQUEST
DBMS_JOB.SUBMIT (JOB_ID1,'satadmin.NAR_OLS_PKG.DRA_REQUEST_PRC;', sysdate,null);
dbms_output.put_line('JOB_ID1 -'||JOB_ID1||' '||'DRA_REQUEST_PRC');
commit;
---- 2. Populating REQUEST_EVENT
DBMS_JOB.SUBMIT (JOB_ID2,'satadmin.NAR_OLS_PKG.DRA_REQUEST_EVENT_PRC;', sysdate,null);
dbms_output.put_line('JOB_ID2 -'||JOB_ID2||' '||'DRA_REQUEST_EVENT_PRC');
commit;
....
....
While TRUE Loop --check all the jobs completed execution
...
End Loop;
END DRA_POPULATE_PRC;

I started executing the procedure DRA_POPULATE_PRC, after that when I queried USER_JOBS am able to see all the jobs have successfully been submitted. But what is happening is that not all the submitted jobs completing the execution. 2 or 3 jobs used be there in the job queue for a long time but not initiating the execution at all if I wait for 2-3 hrs also. I am maintaining small control table(inside the submitted procedures) just to track all the tables load. When the jobs are running if I query this control table I will be able to know what are all the tables load is going on currently, what time the load started , completed etc..

The problem is DRA_POPULATE_PRC is not completing the execution
since I have small While check in the procedure in which I have put a logic(using control table) in such a way that the procedure DRA_POPULATE_PRC should come out(from execution) only after all the submitted jobs completed execution successfully.

I hope, I have explained my problem. Could anyone please help me ASAP.

Thanks a lot in advance
Manju Siddaramegowda

[MERGED by LF]

[Updated on: Thu, 23 April 2009 07:55] by Moderator

Report message to a moderator

Re: Facing a problem while using DBMS_JOB [message #399644 is a reply to message #399636] Thu, 23 April 2009 05:00 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's your job_queue_processes set to?
Re: Facing a problem while using DBMS_JOB [message #399661 is a reply to message #399636] Thu, 23 April 2009 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the value of job_queue_processes parameter?

Regards
Michel
Re: Facing a problem while using DBMS_JOB [message #399668 is a reply to message #399636] Thu, 23 April 2009 07:21 Go to previous message
manju.siddaramegowda
Messages: 3
Registered: July 2008
Location: Bangalore
Junior Member
Hi,

It has been set to 20. Please see below.

SQL> show parameter job_queue_processes

NAME TYPE VALUE
------------------------------------ ----------- ----------------
job_queue_processes integer 20
SQL>

Thanks,
Manju
Previous Topic: UTL_FILE and invalid directory path
Next Topic: runing sql script periodically
Goto Forum:
  


Current Time: Thu Dec 08 12:09:28 CST 2016

Total time taken to generate the page: 0.09272 seconds