Web Development

Ask something
Discussion Locked

Managing Oracle job queues

0 helpful
8 replies

by rodsherer

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In Oracle there is a facility which allows the DBA to set the job intervals and job processes. These tell how many jobs can run on the instance, and how often to check for waiting jobs.

Is there a way to get more granular in this management. Can you tell Oracle that you will allow 5 processes to run, but user A can only have one job running, User B can have 2 jobs running and user C can not run jobs. In other words, I am looking for not only instance specific process settings, but schema level process settings. What I really want is for each schema to be allowed to run only one job out of the queue at a time. But several schemas can each have a job running simultaneously.

8 replies

by gerrit scholten

You can use the DBMS_APPLICATION_INFO package to make information about your process instantly available through the V$SESSION datadictionary view - without doing a COMMIT; in your code.

By querying V$SESSION user A can verify that he/she is not yet running another process.

Btw - Thomas Kyte has some usefull info on the use of DBMS_APPLICATION_INFO in his book "Oracle one-on-one".

by rodsherer

How would I use this to allow oracle to manage how many job queue processes can run on a given schema?

by rodsherer

If a more granular control for the processes is not available, then I need to at least be able to monitor what is running in a given schema. What would be the most reliable way to evaluate what is running via the oracle job queue in a given schemawithin an instance (from within that schema) which will work on both Oracle 8i and 9i?

E.G. There is SCHEMA1 and SCHEMA2 in an instance, and the instance allows 4 job queue processes. From within SCHEMA1, I want to find out (reliably) what has been launched by the oracle scheduler and is currently running for SCHEMA1. What is running in SCHEMA2 does not matter. I am only interested in finding processes launched via the Oracle Job Queue in SCHEMA1.

by draegor

Hi Rod

I can't give you the best answer, but you could submit the question to the oracle mailing list.

Here's the subscribing info.

http://www.orafaq.com/afl-oral.htm

They will have a good idea for you or else be able to point you in theright direction.

by rodsherer

I've gone to Oracles duscussion boards with this, and so far nobody has been able to show me anything of use. Right now I am looking to handle it with code, although I would prefer not to. You would think they would have schema level control.

This discussion is closed to new replies.