I ran the script below : ---------------------------------------------------------------------------- ------------- 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 STATUS = 'INVALID' and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE','FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME / ---------------------------------------------------------------------------- -----------
I get the following error: ERROR at line 8: ORA-01436: CONNECT BY loop in user data Whats this error ??????? -rM -----Original Message----- Trassens Sent: Tuesday, December 18, 2001 2:05 PM To: Multiple recipients of list ORACLE-L You can use the $OH/rdbms/admin/utlrp.sql even in 7.X. However in this last case you must edit and change where you find type# for type without #. App has a similar procedure. Regards. --- Glenn Travis <[EMAIL PROTECTED]> wrote: > If you've got a big schema (READ: Apps), this will > cripple you. Been there, done that, and will never > do it again in an Apps database. > > I have found it always better to generate my own > recompile sql (see script at bottom of my notes). > > Footnote: Read the docs for 8.1.7 as Oracle has > added another parameter to this command. > > DBMS_UTILITY.COMPILE_SCHEMA (<schema>, FALSE); > > The newer version of DBMS_UTILITY.COMPILE_SCHEMA > (<schema>) has an extra boolean argument which > defaults to TRUE (compile everything) for backwards > compatibility, However, due to the problem of object > dependancies being circular in the sys.dependancy$ > table (causing the original problem with > sys.ORDER_OBJECT_BY_DEPENDENCY view), Many objects > are actually INVALIDATED by the default usage. > > Solution: > Running DBMS_UTILITY.COMPILE_SCHEMA (<schema>, > FALSE) will only compile the INVALID objects and > seems to work much better. > > RECOMPILE SCRIPT: > > set heading off > set pagesize 0 > set lines 79 > set verify off > set echo off > set feedback off > > spool comp_all.tmp > 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 > STATUS = 'INVALID' and > OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', > 'FUNCTION', 'PROCEDURE', > 'TRIGGER', 'VIEW' ) > order by > DLEVEL DESC, > OBJECT_TYPE, > OBJECT_NAME; > > spool off > > @comp_all.tmp > > > > > -----Original Message----- > > From: Mohan, Ross [mailto:[EMAIL PROTECTED]] > > Sent: Monday, December 17, 2001 2:25 PM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: RECOMPILE INVALID OBJECTS > > > > > > just out of curiousity, does this buy one > something extra over/above: > > > > execute dbms_utility.compile_schema('<user>'); > > > > > > > > > > > > > > -----Original Message----- > > Sent: Monday, December 17, 2001 1:58 PM > > To: Multiple recipients of list ORACLE-L > > > > > > -------------------- start > ------------------------------ > > set heading off > > set pagesize 0 > > set linesize 79 > > set verify off > > set echo off > > spool recomp_all.tmp > > 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 > > STATUS = 'INVALID' and > > OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', > 'FUNCTION', 'PROCEDURE', > > 'TRIGGER', 'VIEW' ) > > order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME; > > spool off > > > > @recomp_all.tmp > > ---------------- end ---------------------------- > > 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! > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Mohan, Ross > > 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: Glenn Travis > 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). ===== ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __________________________________________________ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Rahul Mehendale 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).
