DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: dbms_job

  1. #1
    Join Date
    Sep 2000
    Posts
    12
    I have a job that does not run automatically. It runs fine when I execute dbms_job.run(); but it never runs on the "next_date". After executing with dbms_job.run the next_date looks correct but it never runs.

    The init.ora file has the following settings:
    job_queue_processes = 2
    job_queue_interval = 10

    Here is the job:
    BEGIN
    dbms_job.submit(
    :jobno,
    'owner.proc();',
    sysdate,
    'SYSDATE + 10/1440'
    );
    commit;
    END;
    /

    Any ideas?

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Delete it with dbms_job.remove() and run in SQL*Plus:

    variable jobno number;
    begin
    dbms_job.submit(:jobno,'owner.proc();',trunc(sysdate) + 10/1440,'trunc(sysdate+1) + 10/1440');
    commit work;
    end;
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Sep 2000
    Posts
    12
    Thanks for the info but that didn't seem to work either. What it did was schedule the next_date for today at 12:10 with an interval of (sysdate + 1) + 10/1440.

    What I want is for it to run immediately with an interval of 10 minutes. No matter what I try, it does not run immediately or even run on the next_date.

    Thanks!

  4. #4
    Join Date
    May 2002
    Posts
    37
    I created job as follows:

    VARIABLE JOBNO NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT(:JOBNO,'BEGIN UPDATE JOB_TEST SET RUNCOUNT = RUNCOUNT+1; COMMIT; END;',
    SYSDATE,'SYSDATE + 2/1440');
    END;
    /

    commit;

    /I assume you ran commit after you created job/

    this job increments runcount in job_test table. it executed immediately after I created it and ran every 2 min until I removed it.

    by the way, job_queue_interval = 10 is a 10 sec interval for SNPn background process to wake up and check for jobs to run.
    So, your init.ora parms look OK.

  5. #5
    Join Date
    Sep 2000
    Posts
    12

    Unhappy

    I'm baffled! Dave, I did exactly what you did (except I modified the update statement for my use) and it is still not executing. I can run the job manually (dbms_job.run) but it will not run by itself.

    Any other ideas???

  6. #6
    Join Date
    Jul 2000
    Posts
    521
    Do you have snp daemon processes running ?
    svk

  7. #7
    Join Date
    Sep 2000
    Posts
    12
    Sorry, how can I tell?

  8. #8
    Join Date
    Jul 2000
    Posts
    521
    Whats you "job_queue_processes" init parameter value ?

    And snp processes are just like other database daemons like PMON,SMON. So, you should be able to see if ora_snp is running.

    If not (job_queue_processes is set to zero), change the value to at least 1. You don't need to bounce the database to do this. You can say "alter system set job_queue_processes=1" and the daemon will start.
    svk

  9. #9
    Join Date
    May 2002
    Posts
    37
    to see all background processes

    select * from V$BGPROCESS;

    regards

    Dave.

  10. #10
    Join Date
    Sep 2000
    Posts
    12
    There are two snp processes running which makes sense because the init.ora file has the following parameters:

    job_queue_processes = 2
    job_queue_interval = 10


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width