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 <Glenn.Travis_at_sas.com> 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:MohanR_at_STARS-SMI.com]
> > 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: MohanR_at_STARS-SMI.com
> >
> > 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: ListGuru_at_fatcity.com (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: Glenn.Travis_at_sas.com
>
> 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: ListGuru_at_fatcity.com (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
ctrassens_at_yahoo.com
christian_trassens_at_yahoo.es
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: ctrassens_at_yahoo.com
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: ListGuru_at_fatcity.com (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: rahul.mehendale_at_tatainfotech.com
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: ListGuru_at_fatcity.com (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).
Received on Tue Dec 18 2001 - 04:42:19 CST
