I also took a quick look at the dbms_pile_schema code and could not see where it obtains the objects correct order to compile and therefore I am not sure if it actually works. I therefore created my own recompile procedure (choice 3) to avoid this problem and have been using it ever since. I tried using it in Oracle 8i but it produces problems if the schema had any java objects. The parameter ‘compile_all’, if true (default) it will compile all the objects regardless if valid or invalid, if set to false it will only recompile invalid objects. The parameter ‘schema’ is the schema whose objects you wish to compile. In the supplied package DBMS_UTILITY one finds a procedure COMPILE_SCHEMA (schema varchar2, compile_all boolean default TRUE).I personally do not use it as it recompiles all the invalid objects in the whole database and I prefer to recompile only one particular schema at a time. The script is used by Oracle after a migration or upgrade. To run it you must be connected as sysdba and it must be run in sql*plus. This script recompiles all invalid objects in the database, not only one particular schema but every invalid object in the database. In $ORACLE_HOME/rdbms/admin you’ll find a sql script called utlrp.sql.To be able to recompile all invalid objects in one go you need to compile the objects in the correct order of dependency. Although invalid PL/SQL modules get automatically recompiled on use, it is useful to ensure that all objects are valid ahead of time, this will eliminate or minimize subsequent latencies caused due to on-demand automatic recompilation at runtime.
The reason for the objects not all becoming valid the first time round is because in Oracle all objects that are dependant on another object that has become invalid it too becomes invalid. This he had to do 3 or 4 times after each other before all the objects were valid. I recently witnessed a developer recompiling his schema’s INVALID objects by selecting all the invalid objects from the user_objects table and compiling them.
0 kommentar(er)
