I had a cron job scheduled to do this... okay, I hate plugging books on this list (if they are mine I mean), and I will happily provide the script for free, but it IS documented, with the script on the CD in the book SQL & PL/SQL Annotated Archives.
If you want the script to generate the sql to revalidate, email me OFFLIST --- Richard Ji <[EMAIL PROTECTED]> wrote: > I like the donation jar idea. Someone owes me $5 from work today > already. > :) > > -----Original Message----- > Sent: Wednesday, January 08, 2003 11:19 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > You could consider scheduling a job which queries all_objects, based > on > object_type and status to locate the invalid objects. The job could > then > create some dynamic SQL to recompile them. I'm not so sure if you > could > place a trigger anywhere to do this automatically but you could > schedule > something to run fairly frequently and email you if it found anything > invalid. > > Hmm - I wonder if you could place a trigger on all_objects (or at > least an > underlying table somewhere) to watch for update of status? guru's? > > The only other solution I can think of is a donation jar. Each time > a DBA > forgets they add $5 (convert to appropriate currency/value) into the > jar. > Perhaps they'll start remembering to check? > > Cheers, > Mark. > > > > > > "shuan.tay\(PC > > I¾G¸R³Ô\)" To: Multiple recipients > of list > ORACLE-L <[EMAIL PROTECTED]> > <shuan.tay@pci cc: > > .co.id> Subject: invalid > procedure/trigger > Sent by: > > [EMAIL PROTECTED] > > om > > > > > > 09/01/2003 > > 13:58 > > Please respond > > to ORACLE-L > > > > > > > > > > Dear all DBAs, > > Is there any way to check whether the procedures or triggers is > valid? > and if it's not valid, recompile it automatically. > Some of the DBAs here always forgot to check procedures/triggers > after > modify table. > > Thanks in advance. > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >>>> > Privileged/Confidential information may be contained in this > message. > If you are not the addressee indicated in this message > (or responsible for delivery of the message to such person), > you may not copy or deliver this message to anyone. > In such case, you should destroy this message and kindly notify the > sender > by reply e-mail or by telephone on (61 3) 9612-6999. > Please advise immediately if you or your employer does not consent > to > Internet e-mail for messages of this kind. > Opinions, conclusions and other information in this message > that do not relate to the official business of > Transurban City Link Ltd > shall be understood as neither given nor endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >>>> > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >>>> > Privileged/Confidential information may be contained in this message. > > If you are not the addressee indicated in this message (or > responsible for > delivery of the message to such person), you may not copy or deliver > this > message to anyone. > In such case, you should destroy this message and kindly notify the > sender > by reply e-mail or by telephone on (61 3) 9612-6999. > Please advise immediately if you or your employer does not consent to > Internet e-mail for messages of this kind. > Opinions, conclusions and other information in this message that do > not > relate to the official business of Transurban City Link Ltd shall be > understood as neither given nor endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >>>> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Richard > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Richard Ji > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).