I see tons of people using their our scripts to validate system objects, but how about..
SQL> exec dbms_utility.compile_schema( 'SCHEMA_NAME', false );
PL/SQL procedure successfully completed.
SQL>
..talk about using built-in funtionality.
December 28, 2005 - 5:53 pm UTC
Cut and pasted from Expert one on one Oracle - the compile schema "can work", does more work than it needs to - and sometimes won't work (and sheds light on the ORDER_OBJECT_BY_DEPENDENCY - which is probably what the original poster meant)
Compile_Schema
The goal of the COMPILE_SCHEMA procedure is to attempt to make valid all invalid procedures, packages, triggers, views, types, and so on in a schema. This procedure works in Oracle 8.1.6 by using the SYS.ORDER_OBJECT_BY_DEPENDENCY view. This view returns objects in the order they depend on each other. In Oracle 8.1.7 and higher, this view is no longer used (why this is relevant will be shown below). If we compile the objects in the order this view returns them at the end, all objects that can be valid, should be valid. This procedure runs the ALTER COMPILE command as the user who invoked the procedure (invokers rights).
It should be noted that COMPILE_SCHEMA demands you pass in a case sensitive username if you call:
scott@TKYTE816> exec DBMS_UTILITY.compile_schema( 'scott' );
It is probable that nothing will happen (unless you have a lower case user named scott. You must pass in SCOTT.
There is however another issue with COMPILE_SCHEMA in 8.1 versions of the database prior to 8.1.6.2 (that is all 8.1.5 and 8.1.6.0 and 8.1.6.1 versions). If you have a Java enabled database, this will introduce some recursive dependencies into your system. This will cause COMPILE_SCHEMA to raise the error:
scott@TKYTE816> exec dbms_utility.compile_schema( user );
BEGIN dbms_utility.compile_schema( user ); END;
*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "SYS.DBMS_UTILITY", line 195
ORA-06512: at line 1
This is coming from the SYS.ORDER_OBJECT_BY_DEPENDENCY view and is the reason why Oracle 8.1.7 and up do not use that view. If you encounter this error, we can create our own COMPILE_SCHEMA procedure that behaves exactly as the real COMPILE_SCHEMA. We can do this by compiling the objects in any order we feel like it. It is a common misconception that we must compile objects in some specific order we can in fact do them in any arbitrary order and end up with the same outcome we would have had ordered by dependency. The logic is:
1. Pick any invalid object from a schema that we have not yet tried to compile
2. Compile it
3. Go back to step one until there are no more invalid objects that we have not yet tried to compile
It is that simple we need no special ordering. That is because a side effect of compiling an invalid object is that all invalid objects it depends on will be compiled in order to validate this one. We just have to keep compiling objects until we have no more invalid ones (well, we might have invalid ones but that would be because they cannot be successfully compiled no matter what). What we might discover is that we need only to compile a SINGLE procedure to have 10 or 20 other objects get compiled. As long as we don't attempt to manually recompile those 10 or 20 other objects (that would invalidate the first object again) we are OK.