We use a job scheduling system that runs on top of DBMS_JOB. It uses the main task to create one-time tasks. We deploy the same set of tasks for all our clients, but we can specify which tasks should be performed only on specific clients.
We are having occasional problems with a work-dependent process. The main reason for this is UTL_TCP, not the timeout when it receives the expected response. I want to be able to kill these tasks so that they can start again.
I am looking to create a new task that kills any of these one-time tasks that take longer than a certain time.
We have been stuck with Oracle 10g for some time, so I am limited in what it can do.
There's an article that seems to cover most of this in
http://it.toolbox.com/blogs/database-solutions/killing-the-oracle-dbms_job-6498
I have a feeling that this will not cover all possible events, including:
- We run work tasks as several different users, and the user can only split / delete created tasks. I believe that I can use DBMS_IJOB to get around this, but I need to get the DBA to let me execute it.
- We have Oracle RAC systems. I understand that 10g limits the ALTER SYSTEM KILL SESSION to killing sessions in the current instance. I could organize the execution of all tasks on one copy, but I have not tried it yet.
Anything else I should consider? Stack overflow requires a definitive answer to this question.