Darn invalid objects dependencies!
Joel Garry
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...
Thomas Kyte
joel-...@home.com says...
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 (tk...@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
Norman Dunbar
-----Original Message-----
From: Thomas Kyte [mailto:tk...@us.oracle.com]
Posted At: Saturday, October 13, 2001 2:28 PM
Posted To: server
Conversation: Darn invalid objects dependencies!
Subject: Re: Darn invalid objects dependencies!
>> The first execution should just
>> specify the schema name and the second should set the "compile_all"
flag to
>> FALSE.
SQL> exec dbms_utility.compile_schema(
'SCHEMA_NAME_YOU_WANT_TO_COMPILE', FALSE);
Hi Thomas,
I've just tried this out - and it works, but in the docs for 8.1.7,
there is no mention of the second parameter, so thanks for that little
'insider' bit of information. I know it doesn't work on 8.0.5, but I'm
off to test out 8.1.5 now :o)
Cheers,
Norman.
------------------------------------------------------------------------
-----
Norman Dunbar EMail: Norman...@LFS.co.uk
Database/Unix administrator Phone: 0113 289 6265
Fax: 0113 289 3146
Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
------------------------------------------------------------------------
-----
Joel Garry
Thanks Tom. Could you address the issue of when or why it doesn't work? I just
don't have the warm fuzzies about it, it having had bugs and a published
work-around and all. I don't quite get the "why" of setting the
flag to false. Is there a problem if I always run it twice whether or not
there are invalid objects the second time? Will this carry over to 9?
jg
--
Just full of questions this morning.
Joel Garry
>
> 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.
OK, I started with 32 invalid views, 16 each in 2 schemata. Then:
SQL>
SQL> exec dbms_utility.compile_schema( 'FINPROD') ;
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
35
SQL> exec dbms_utility.compile_schema( 'FINPROD') ;
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
35
SQL> exec dbms_utility.compile_schema( 'MANPROD' );
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
38
SQL> exec dbms_utility.compile_schema( 'MANPROD', FALSE);
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
38
SQL> alter view manprod.OBSCURED_FOR_POSTING compile;
View altered.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
37
SQL> spool joel_is_frustrated
SQL> select 'alter '||object_type||' '||owner||'.'||object_name||'
compile;' from dba_objects where status = 'INVALID';
SQL> spool off
SQL> @joel_is_frustrated.lst
SQL> select object_name from dba_objects where status = 'INVALID';
no rows selected
So I'm allowed to compile them. I have the final release of 8i with
something
basic that doesn't seem to work. Now what?
jg
--
Off to wrassle with other gators.