Re: Darn invalid objects dependencies!

In article <91884734.0110121543.302acd2f_at_posting.google.com>, joel-garry_at_home.com says...
>
>I am attempting to script some data movement from 8.0.5 to 8.1.7. So
>I need a script that can recompile everything that it finds that is
>invalid. Naturally, I had a cute one pass called comp_all from metalink
>(having fixed its self-immolation qualities, of course), that used
>the order_object_by_dependency view. Of course, that has been taken
>away because of the wonderful java stuff, and the dbms_util package that
>is supposed to do it has bugs so it doesn't always validate everything,
>and 8.1.7 is the last 8i... "fixed in a future release" as I once posted
>in a rec.humor.funny posting.
>
>So does anybody have a script that can do this, or do I go back to the
>old 7 way of a script that compiles everything invalid and just run it
>a bunch of times and hope for the best?
>
>Metalink Note:121153.1 seems kind of... I don't know, hacked or something.
>
>jg
>--
>You'd think they would have figured it out by now...

how about:

SQL> exec dbms_utility.compile_schema( 'SCHEMA_NAME_YOU_WANT_TO_COMPILE' );

(make sure to use upper case, as the schema name is case sensitive in that function call). If there are remaining "invalid" objects after the compile, simply:

Execute dbms_utility.compile_schema() twice. The first execution should just specify the schema name and the second should set the "compile_all" flag to FALSE. So, just:

SQL> exec dbms_utility.compile_schema( 'SCHEMA_NAME_YOU_WANT_TO_COMPILE', FALSE );

after that first one.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp