Re: Darn invalid objects dependencies!

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9q9fg30bbb_at_drn.newsguy.com>...

>
> 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.