Results 1 to 11 of 11

Thread: jobs

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unanswered: jobs

    Code:
    DBMS_JOB.SUBMIT(tes, 'escalate('||x||');', SYSDATE+0.000472,null);
    the above code submits a job, i can see it in the job list. It gets submitted fine, but it never seems to run.

    How can I tell when it was last executed, and the results from that execution.

    The procedure escalate just sends an email (the email sending definatly works). I haven't included it becuase it works --> if i type exec escalate(72); @ the sqlplus prompt it sends the email fine.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: jobs

    Did you COMMIT after the SUBMIT? The job record is not visible to the job processor until then.

    The view USER_JOBS shows information like LAST_DATE.

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Apparently I cant commit after the above script was run because it was called inside a trigger.
    The job IS in the list, thats for sure.

    However, I have looked at the last dates for each job and non of them have run - the column is null.
    Last edited by rhs98; 05-07-03 at 10:34.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by rhs98
    Apparently I can commit after the above script was run because it was called by a trigger.

    However, I have looked at the last dates for each job and non of them have run - the column is null.
    Not sure whether you meant to say "can" or "can't" commit. You can't commit IN a trigger, but you must commit at some point AFTER the statement that fired the trigger. The job will not run until the transaction is committed.

    If you mean you HAVE committed but still it doesn't run, then it's time to check the system parameters:

    SQL> select name, value
    2 from v$parameter
    3 where name like '%job_queue%'
    4 /

    NAME
    ----------------------------------
    VALUE
    ----------------------------------
    job_queue_processes
    20

    job_queue_interval
    30

    job_queue_processes indicates how many jobs can run at any time, so it needs to be greater than zero.

    job_queue_interval indicates how frequently the job queue is checked for jobs to run (in seconds)

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Genius!


    job_queue_processes ----> 0

    how do i change it? I am looking now (gonna try an update!)

    thanks yet again Andrew you a genius!

    [btw: did mean cant commit in trigger]

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by rhs98
    Genius!


    job_queue_processes ----> 0

    how do i change it? I am looking now (gonna try an update!)

    thanks yet again Andrew you a genius!

    [btw: did mean cant commit in trigger]
    The command is:

    ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>;

    You'll need DBA privileges to do that (I guess).

  7. #7
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    noice, thanks!

    I am the 'DBA' <----- !!!!

    I need to be able to have > a hundred jobs going...is that a really bad idea or not?

    They will only fire 5 minutes after generation. Is there anyway to set the jobs to delete themselves, as they only run once?

  8. #8
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    basically do i need one process per running job, or once process per job that may run?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by rhs98
    noice, thanks!

    I am the 'DBA' <----- !!!!

    I need to be able to have > a hundred jobs going...is that a really bad idea or not?

    They will only fire 5 minutes after generation. Is there anyway to set the jobs to delete themselves, as they only run once?
    I'm not a DBA, so not best placed to answer this! But I guess having 100 jobs running simultaneously is just like having 100 users log in to SQL Plus and run the procedure that the job is running. If the job is a resource-hungry monster query, that could be a problem; otherwise, perhaps OK.

    If a job doesn't have a NEXT parameter to make it re-run, it will disappear from the queue after it completes.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by rhs98
    basically do i need one process per running job, or once process per job that may run?
    One per running job. So if job_queue_processes is set to 20, and you submit 100 jobs, then Oracle will start 20 Jnnn processes which will start running the first 20 jobs. As each process finishes a job it will look for another in the queue and run that, and so on until all 100 jobs have been run.

  11. #11
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    When I say I am a DBA, I mean I am the DBA (i.e. I am acting as one, but am not actually one)

    The procedure it runs is not very resource hungry and is quite simple, it just updates one row and sends an email! Not too bad.

    Thanks again Andrew!

Posting Permissions

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