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.
bencouve2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vishal68Commented:
You can use

execute dbms_job.run(jobid)

where jobid is the jobid from dba_jobs view.

HTH
Vishal
RCorfmanCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bencouve2Author Commented:
Thanks for all the input. I have had a few strange things happen. Firstly I did the execute dbms_job.run(jobid) 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 dbms_job.run(jobid), and ran execute dbms_Job.broken(jobno,false); , checked the BROKEN flag was set to 'N', which it was, and then ran the execute dbms_job.run(jobid) 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 dbms_job.run(jobid); 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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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.
vishal68Commented:
The dbms_job.run 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 dbms_job.run, you don't need to set the broken flag to N, if the job is successfully completed the dbms_job.run 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.

HTH
Vishal
 
bencouve2Author Commented:
Thanks for your help guys.
RCorfmanCommented:
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.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#ARPLS019
Here is a link in the administrator's guide. I think this is where you really need to look for more information.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#ADMIN010
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.