Starting jobs in Oracle

I have 3 jobs that need to be running in my database but they are not. If I do a

select * from dba_jobs_running;

Nothing is returned. I can see the jobs in dba_jobs. Can I start these jobs by just running the

begin trc.function('pipe_name'); end;

This is what I can see in the dba_jobs table under the WHAT column ?

Any advice would be well received. Thanks in anticipation.
Who is Participating?
Sure. The jobs have a next_date and next_sec column that show when it is scheduled to run. Also a 'broken' column. If broken shows 'Y', then they will never run.

If it is broken, you can use
dbms_Job.broken(jobno,false) to 'fix' it.

If the next_date is in the future, and you want it to run, use
dbms_job.next_date(jobno,sysdate) to set it to run right away.

Oh, after using dbms_job package items, you must 'commit' for them to take effect.

If there is a value in this_date, then the job is actually running right  now.

If the job(s) look like they are ready to run, but not launching, there is a system parameter that sets how many jobs are allowed to be running at any one time.  The default for this is zero I believe so they will never launch.

job_queue_processes is the init parameter. Set it to 2,4, whatever is appropriate. This is used to throttle the number at once. We use jobs on our database extensively... we have AQ with callbacks, they use jobs, I believe streams, and replication schedules do to... Anyway, we have ours set to 36... this just depends on your situation.
You can use


where jobid is the jobid from dba_jobs view.

bencouve2Author Commented:
Thanks for all the input. I have had a few strange things happen. Firstly I did the execute that vishal68 recommended and it seemed to hang for a while. I did a select on dba_jobs_running while it was hanging and it showed that the job was running but when I did the select on dba_jobs it indicated that the job was broken so I killed the execute, and ran execute dbms_Job.broken(jobno,false); , checked the BROKEN flag was set to 'N', which it was, and then ran the execute again. But once again it hangs and the dba_jobs_running indicates that the jobs ( I have done this for 3 jobs) are running but the execute; sessions are running and I run two from SQLNavigator and one from unix. Is this ok, or should I do something else? Thanks again for all your help. I think I am almost there.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bencouve2Author Commented:
Just one other thing,

should I not use dbms_job.submit(..

I am not sure of the syntax for this. Could you advise on this, please.
The is equivalent to running the job manually, i.e. session will continue to be there till the job completes. You can monitor the same using another session and checking dba_jobs_running. Also when you are using, you don't need to set the broken flag to N, if the job is successfully completed the will reset the flag to N automatically.

Also I suggest that you consider what  RCorfman has suggested to check that your system is properly setup for running the jobs automatically. What I had suggested was just a command to manually run the jobs as asked by you. But if you are seeing the jobs as broken then you need to find the reason for the failed jobs. You can check the alert log of your database for finding the reason for failure. Fix the problem and then check whether the jobs are automatically run or not.

One more thing after fixing the problem, you may need to either set the broken flag as N or run the jobs manually once.

bencouve2Author Commented:
Thanks for your help guys.
I know it is closed, but I'm not sure it is all cleared up, and am happy to continue answering some questions relative to dbms_job.
First, I would read the documentation on the package. I cannot emphasize enough that Oracle has some reasonably good documentation on this package. Register on OTN.Oracle.Com if you haven't, this is where their documentation is at. DBMS_JOB hasn't changed much in the last several releases. In 10G, they have a scheduler package that is more robust than dbms_Job, but I haven't heard dbms_job is actually going away.

You may well need to use dbms_job.submit in the future. "Submit" creates a new job, "Run" executes and existing job.  vishal68 indicated correctly, whenever a job finishes without failure, it is marked as Broken=N.

Each time a job attempts to execute, but fails, the information on the failure is put out in one of the Dump directories... I'm not a dba, but I'm pretty sure it is UDUMP.... maybe BDUMP, but it is one of them.  This will show the ORA-nnnnn failure that occured on the job.

When it failes, the FAILURES is incremented by one, Oracle uses an algorithm to decide when to retry, then it executes the job again, eventually. When it finally gets to 16 Failures, the job goes to 'Broken' state, and it stops trying to execute.

If your job showed as broken, it means that at some point in time, it was failing, failed 16 times, and the flag was set.

Here is a link to the dbms_job package documentation for 9iR2.
Here is a link in the administrator's guide. I think this is where you really need to look for more information.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.