Fixing Jobs with Time 1

[mwolf00]

I am delivering a script that will be run by a customer. At the beginning of the script, I break the jobs so that they will not start during the script execution. At the end of the script, I am fixing the jobs. The only problem that I am having is that a job that is fixed executes immediately unless you set the next_date. I want to populate the value of next_date with the current value of INTERVAL from user_jobs. Is there any way to do that?

This is the closest I can come but it fails at compilation:

CREATE OR REPLACE PROCEDURE job_fixer
AS
   CURSOR broken_jobs_cur
   IS
   SELECT job, interval
     FROM user_jobs
    WHERE broken = 'Y';
  dtNext  date; 
  strQuery VARCHAR(200);
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      strQuery := 'select '|| job_rec.interval || ' INTO dtNext from dual';
      execute immediate(strquery);
      
      DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>dtNext);
   END LOOP;
END job_fixer;

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[carp]

I would suggest creating a holding table such as

CREATE TABLE job_snapshot AS SELECT * FROM user_jobs;

Then go ahead and break your jobs. When you go to unbreak them, you can pull the next_date from job_snapshot. Once you are done with breaking/unbreaking jobs, drop the job_snapshot table.

 

[mwolf00]

Thank you for the reply. My issue is that I cannot seem to unbreak the job with a value in the next_date field. The value is there in the USER_JOBS table in the INTERVAL field event after the jobs is broken. The problem I am having is that the value is a text field like 'SYSDATE + (1/24)' and I cannot get that value back to an actual date.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[mwolf00]

I was able to develop a solution:

CREATE OR REPLACE PROCEDURE job_fixer
AS
   CURSOR broken_jobs_cur
   IS
   SELECT job, interval
     FROM user_jobs
    WHERE broken = 'Y';
  dtNext  date; 
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      execute immediate('select ' || job_rec.interval || ' FROM DUAL') into dtNext;
      DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>dtNext);
   END LOOP;
END job_fixer;

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[carp]

It seems to me that you are confusing the next_date with the interval. I just double-checked, and the next_date field is a DATE datatype. When you break the job, interval is untouched, so you don't have to touch it. So if you save the next_date BEFORE you break the job, then you should be able to do a simple lookup to get the original next_date to put in when you unbreak the job. Something like:

CREATE OR REPLACE PROCEDURE break_jobs IS
   l_cmd       VARCHAR2(1000);
   l_next_date DATE;
   l_name      VARCHAR2(30) := USER||'_jobs';
BEGIN
   -- SAVE THE DATA 
   EXECUTE IMMEDIATE 'CREATE TABLE '||l_name||
                     ' AS SELECT job, next_date
                       FROM user_jobs 
                       WHERE broken_flag = ''N''';
   -- NOW BREAK THE JOBS
   FOR i IN (SELECT job 
               FROM user_jobs 
              WHERE broken_flag = 'N') LOOP   
      dbms_jobs.broken(i.job, true, null);
   END LOOP;
   COMMIT;
END break_jobs;

Once you're done with your work, unbreak them:

CREATE OR REPLACE PROCEDURE unbreak_jobs AS
   l_name VARCHAR2(30) := USER||'_jobs';
   TYPE job_rec IS RECORD(job NUMBER, next_date DATE);
   TYPE job_tab IS TABLE OF job_rec INDEX BY PLS_INTEGER;
   l_jobs job_tab;
   l_cmd VARCHAR2(500);
BEGIN
   l_cmd := 'SELECT * FROM '||l_name;
   EXECUTE IMMEDIATE l_cmd BULK COLLECT INTO l_jobs;
   FOR i IN l_jobs.FIRST..l_jobs.LAST LOOP
      dbms_jobs.broken(l_jobs(i).job,false,l_jobs(i).next_date);
   END LOOP;
   COMMIT;
END unbreak_jobs;

 

[carp]

The only problem I see with your solution is that a scheduled run might be lost.
Suppose the interval is SYSDATE + 7 (for a weekly job), and the next date is tomorrow. If you break it today and then reenable it in an hour, you are going to be setting the next run for sometime next week. Consequently, you may be disrupting a critical schedule.

I think it would be better to set the next_date to its original value. I believe if the job is broken when it was supposed to run, it will run when you reenable the job and Oracle realizes it is overdue (you will probably want to experiment with this to confirm actual behavior).

 

[mwolf00]

carp,

What I've actually seen is that the job will run immediately when it is unbroken whether is is scheduled to run or not. Fortunately all of my jobs run either hourly or nightly. The hourly ones will run an hour after everything is enabled again and the nightly ones will run the night that they are unbroken.

Thanks again!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[carp]

Of course, the lazy side of me says why don't you just

ALTER SYSTEM SET job_queue_processes = 5;
[code]
This will suspend all jobs while you do your adjustments.  Then when you are done, 
[code]
ALTER SYSTEM SET set job_queue_processes = 5;

You would naturally want to see what your normal setting is before you go to zero so you can reset it correctly when you are done. This approach has the disadvantage of disabling all jobs instead of just one user's jobs, but is rather nice because it doesn't mess with your jobs table.

 

[mwolf00]

That is interesting. Does it work in 9i?

For now I will stick with the current solution I have.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SantaMufasa]

Dave [Carp],

Did you mean for the first job_queue_processes seting:

ALTER SYSTEM SET job_queue_processes = 0;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[carp]

Yes indeed - the Cut-n-Paste-O-Matic got me again! Also, I meant to add "/code" insteado of "code". Same problem, different symptom. This multi-tasking stuff is greatly overrated!
So the REAL posting should look like this:

ALTER SYSTEM SET job_queue_processes = 0;

This will suspend all jobs while you do your adjustments. Then when you are done,

ALTER SYSTEM SET set job_queue_processes = 5;

You would naturally want to see what your normal setting is before you go to zero so you can reset it correctly when you are done. This approach has the disadvantage of disabling all jobs instead of just one user's jobs, but is rather nice because it doesn't mess with your jobs table.