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>
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
