I'd like to add a point here as well...
Because DBMS_JOBS are asynch, ie not transaction bound and not read-consistent, by design they should not be allowed to be fired from triggers or other event-driven mechanisms _if_ they are to accomplish some transaction-specific action. I can dream up all kinds of goofy crap that developers could accidentally do if they use this. I know, I'm one of them...
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, December 10, 2002 6:14 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:RE: a PL/SQL design question.
>
>
> DBMS jobs are good things for stuff that can occur totally
> within the database
> and on a scheduled basis only. It sounds, RAJ, like your
> developers made a real
> mess. What would have been a better idea would have been to
> have the trigger
> update a table that the job on a scheduled basis checked for
> what it needed to
> do.
>
> Dick Goulet
>
> ____________________Reply Separator____________________
> Author: "Jamadagni; Rajendra" <[EMAIL PROTECTED]>
> Date: 12/10/2002 4:38 AM
>
> Jeremy, as I wholeheartedly agree that dbms_job is a good
> thing, here is my
> recent experience ...
>
> One of the development group recently posed us the question
> "Can we use a db
> trigger to fire a dbms_job to be executed only once?" we
> reluctantly agreed
> only on the condition that this job will not be a repeating
> job as 9012 has
> had its own problems with dbms_job (the server sometimes
> forgets that there
> a jobs to run ...).
>
> The dev team tested it in ACPT and okayed it to go. That
> night I was on call
> and computer room called me to say that the system is very
> slow and one of
> the support person called to say that they were getting
> ora-4030 errors on
> simple selects.
>
> Well I logged on, looked at the system, it showed some load,
> but then I
> looked at dba_job queue and boy there were 14000 jobs sitting
> waiting to be
> run.
>
> bottom line: I shut off job_queue_processes to zero, disabled
> the triggers
> on the tables that submitted these jobs, gave all the details to the
> developer and his manager after waking them up at 2am and
> received a promise
> that they will fix the code tomorrow before 12noon. They did.
>
> The reason, the development team "didn't anticipate" that
> there will be so
> many changes so they didn't optimize their code.
>
> I am all for AQ solution ... though I like dbms_job and they
> do work as
> advertised unless of course you are using 901x where thee are
> some bugs.
>
> My $0.02
>
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect
> that of ESPN Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
> <HTML>
> <HEAD>
> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;
> charset=iso-8859-1">
> <META NAME="Generator" CONTENT="MS Exchange Server version
> 5.5.2654.45">
> <TITLE>RE: a PL/SQL design question.</TITLE>
> </HEAD>
> <BODY>
>
> <P><FONT SIZE=2>Jeremy, as I wholeheartedly agree that
> dbms_job is a good thing,
> here is my recent experience ...</FONT>
> </P>
>
> <P><FONT SIZE=2>One of the development group recently posed
> us the question
> "Can we use a db trigger to fire a dbms_job to be
> executed only once?"
> we reluctantly agreed only on the condition that this job
> will not be a
> repeating job as 9012 has had its own problems with dbms_job
> (the server
> sometimes forgets that there a jobs to run ...). </FONT></P>
>
> <P><FONT SIZE=2>The dev team tested it in ACPT and okayed it
> to go. That night I
> was on call and computer room called me to say that the
> system is very slow and
> one of the support person called to say that they were
> getting ora-4030 errors
> on simple selects.</FONT></P>
>
> <P><FONT SIZE=2>Well I logged on, looked at the system, it
> showed some load, but
> then I looked at dba_job queue and boy there were 14000 jobs
> sitting waiting to
> be run. </FONT></P>
>
> <P><FONT SIZE=2>bottom line: I shut off job_queue_processes
> to zero, disabled
> the triggers on the tables that submitted these jobs, gave
> all the details to
> the developer and his manager after waking them up at 2am and
> received a promise
> that they will fix the code tomorrow before 12noon. They
> did.</FONT></P>
>
> <P><FONT SIZE=2>The reason, the development team "didn't
> anticipate"
> that there will be so many changes so they didn't optimize their
> code.</FONT></P>
>
> <P><FONT SIZE=2>I am all for AQ solution ... though I like
> dbms_job and they do
> work as advertised unless of course you are using 901x where
> thee are some
> bugs.</FONT></P>
>
> <P><FONT SIZE=2>My $0.02</FONT>
> </P>
>
> <P><FONT SIZE=2>Raj</FONT>
> <BR><FONT
> SIZE=2>______________________________________________________</FONT>
> <BR><FONT SIZE=2>Rajendra
> Jamadagni  
> ;  
> ; MIS, ESPN Inc.</FONT>
> <BR><FONT SIZE=2>Rajendra dot Jamadagni at ESPN dot com</FONT>
> <BR><FONT SIZE=2>Any opinion expressed here is personal and
> doesn't reflect that
> of ESPN Inc. </FONT>
> <BR><FONT SIZE=2>QOTD: Any clod can have facts, but having an
> opinion is an
> art!</FONT>
> </P>
>
> </BODY>
> </HTML>
>
>
