Thanks guys . -Bp ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 11:33 AM
> From the oracle Metalink.. > > /* > Doc ID: Note:1036972.6 > DATABASE > Type: SCRIPT > Status: PUBLISHED > Content Type: TEXT/PLAIN > Creation Date: 15-OCT-1997 > Last Revision Date: 27-APR-2000 > Language: USAENG > > ====== > Title: > ====== > > Compiling Invalid Objects in the Database > > =========== > Disclaimer: > =========== > > This script is provided for educational purposes > only. It is not supported by > Oracle Support Services. This script has been tested > and appears to work as > intended; however, you should always test any script > before relying on it. > > PROOFREAD THIS SCRIPT BEFORE USING IT! Due to > differences in the way text > editors, e-mail packages, and operating systems > handle text formatting > (spaces, tabs, and carriage returns), this script may > not be in an executable > state when you first receive it. Check over the > script to ensure that errors > of this type are corrected. > > ========= > Abstract: > ======== > > This script will compile INVALID objects in the > database. > > ============= > Requirements: > ============= > > DBA > > ======= > Script: > ======= > > > -------------cut-------------cut---------------cut-------------- > > > REM Script to compile INVALID Objects in the database > > REM > REM VALIDATE.SQL > > REM > REM This script recompiles all objects that have > become invalidated > REM > REM For proper generation of the log file, this > script should be > REM run after connecting as SYS (or internal) > using SQL*Plus. > REM > REM When run from Server Manager 2.3, all objects > will still be > REM recompiled, but the log file, VALIDATE.LOG, > will contain some > REM error messages. Those error messages are > generated because > REM Server Manager does not understand all of the > SET xxx messages > REM used in this script. > > REM > */ > set pagesize 0 > set linesize 120 > set heading off > set feedback off > set trimspool on > set termout on > select 'Recompiling '||count(object_name)||' invalid > objects.' > from dba_objects where status='INVALID'; > select 'This may take a long time. Please wait...' > from dual; > set termout off > spool validate_objects.sql > select 'spool validate.log' from dual; > select 'set trimspool on' from dual; > > select 'alter ' || decode(object_type, 'PACKAGE > BODY', 'PACKAGE', object_type) > || ' ' || owner || '.' || object_name || ' > compile' > || decode(object_type, 'PACKAGE BODY', ' > body;', ';') > from dba_objects > where status='INVALID' > order by decode(owner, 'SYS', 'A', 'SYSTEM', 'B', > 'C'||owner) asc, > decode(object_type, 'PACKAGE BODY', 'AAA', > 'PACKAGE', 'AAB', > substr(object_type, 1, 3)) desc, > object_name; > > /* REM > REM Compile SYS's objects first, then SYSTEM's, then > the rest. > REM This order by clause will result in compiling > objects > REM in this order: > REM > REM VIEWS, TRIGGERS, PROCEDURES, FUNCTIONS, > PACKAGES, PACKAGE BODIES. > REM > */ > select 'set heading on' from dual; > select 'set feedback on' from dual; > select 'select > substr(rpad(owner||''.''||object_name,40)' from dual; > select ' ||''(''||object_type||'')'', 1, 80) > "Remaining Invalid > Objects"' from dual; > select 'from dba_objects where status=''INVALID'' > order by owner, object_type, > object_name;' from dual; > select 'spool off' from dual; > spool off > > set termout on > set pagesize 25 > set linesize 80 > set heading off > set feedback off > select chr(13)||'Finished recompiling.' from dual; > select chr(13)||'There are '||count(*)||' remaining > invalid objects.' > ||decode(count(*), 0, null, ' Please recompile > manually.') > from dba_objects where status='INVALID'; > set heading on > select substr(rpad(owner||'.'||object_name,40) > ||'('||object_type||')', 1, 80) "Remaining > Invalid Objects" > from dba_objects where status='INVALID' order > by owner, object_type, > object_name; > > =============================================== > > the short version ;) > > select decode( object_type, 'PACKAGE BODY', 'ALTER > PACKAGE ' || OWNER ||'.'||OBJECT_NAME || ' COMPILE > BODY;', > 'ALTER ' || OBJECT_TYPE||' > '||OWNER||'.'||OBJECT_NAME||' COMPILE;' ) > FROM DBA_OBJECTS A, SYS.ORDER_OBJECT_BY_DEPENDENCY B > WHERE A.OBJECT_ID = B.OBJECT_ID(+) and A.STATUS = > 'INVALID' > AND A.OBJECT_TYPE IN ('PACKAGE BODY', 'PACKAGE', > 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) > ORDER BY B.DLEVEL DESC, A.OBJECT_TYPE, A.OBJECT_NAME; > > > I hope this help > > regards > Gabriel > > --- Connor McDonald <[EMAIL PROTECTED]> wrote: > > @?/rdbms/admin/utlrp.sql > > > > hth > > connor > > > > --- "Vergara, Michael (TEM)" <[EMAIL PROTECTED]> > > wrote: > I have written scripts like this in the > > past, > > but > > > the problem > > > I run into is that recompiling some objects > > > invalidates others, > > > sometimes in a circular loop. What I'd like to > > > find/see/learn-how-to-do > > > is a script that compiles things in dependency > > > order. So far, > > > this capability eludes me. I use Oracle 8.0.5 -> > > > 8.1.7.4. > > > > > > Cheers, > > > Mike > > > > > > -----Original Message----- > > > Sent: Saturday, August 17, 2002 8:48 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Script "gen_recompile.sql" online at > > > http://www.EvDBT.com/tools.htm... > > > > > > As written, it will not execute the generated > > > "run_recompile.sql" script; you'll have to > > > uncomment the HOST command at the bottom to do > > that. > > > I like to leave it with it's teeth pulled however > > > -- at least at first -- so I can review the > > > generated script... > > > > > > Hope this helps... > > > > > > ----- Original Message ----- > > > To: Multiple recipients of list ORACLE-L > > > <mailto:[EMAIL PROTECTED]> > > > Sent: Friday, August 16, 2002 4:28 PM > > > > > > I am feeling lazy today .. sooooo if someone can > > > give me script that compiles all invalid objects > > > untill all objects are valid ... will be great . > > Yes > > > I am looking for the one which does this in a loop > > > so that i dont have to run this many times to get > > 0 > > > invalid objects > > > > > > Thanks , > > > -Bp > > > > > > > > > > ===== > > Connor McDonald > > http://www.oracledba.co.uk > > http://www.oaktable.net > > > > "Remember amateurs built the ark - Professionals > > built the Titanic" > > > > __________________________________________________ > > Do You Yahoo!? > > Everything you'll ever need on one web page > > from News and Sport to Email and Music Charts > > http://uk.my.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: =?iso-8859-1?q?Connor=20McDonald?= > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California -- Public Internet > > access / Mailing Lists > > > -------------------------------------------------------------------- > > 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). > > > ===== > "Any dream worth having is a dream worth fighting for"(Cualquier sueño que valga la pena tener, es un sueño por el que vale la pena luchar)Charles Xavier > > __________________________________________________ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Gabriel Aragon > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > 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.com -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).