Recompiling by script

Various scripts exist that can help you reduce the tedium of these recompilations. Oracle's own is called utlrp.sql, distributed in the usual rdbms/admin subdirectory—it works fine, but it does require DBA privileges. Generally, these kinds of scripts are not clever enough to recompile things in reverse dependency order. They simply iterate over the list of invalid programs, compiling each one; then they reread the list of invalid programs and repeat the process either until everything is valid or until recompiling has healed as many as it's going to heal. The scripts do tend to compile all the package headers before the bodies, though, which makes sense.

Solomon Yakobson has produced a recompile utility that does do things in the right order using CONNECT BY START WITH. This script is available from the Quest Pipelines PL/SQL Archive at: http://www.quest-pipelines.com/Pipelines/PLSQL/archives.htm under the header "A More Sophisticated Recompile Utility." It is also available in the recompile.sql file on the O'Reilly site.

 

If you have a large number of recompilations to perform and you have sufficient machine resources—CPUs, memory, disk—you should consider using Oracle's UTL_RECOMP package (found in utlrcmp.sql). This package allows you to recompile invalid objects in parallel, which it accomplishes by submitting multiple recompilation requests into Oracle's job queue. However, even Oracle warns that this package may not yield dramatic results because of write contention on system tables.

A Little Validation, Please? When a database object's DDL time changes, Oracle's usual modus operandi is to immediately invalidate all of its dependents on the local database. From 8.1.7.3 onwards, there is a way to tell Oracle that you want to do a conditional invalidation of PL/SQL—that is, to invalidate dependents only if the object's source code has actually changed. Be warned, though, that this is one of Oracle's undocumented and unsupported features—not for use by customers unless so advised by Oracle Support. You could be completely out of luck if it breaks something. The way to turn on the feature for the current session is: SQL> ALTER SESSION SET EVENTS2 '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; Session altered. Now install your new code, and run a recompilation script such as $ORACLE_HOME/rdbms/admin/utlrp.sql. Then, turn off the feature: SQL> ALTER SESSION SET EVENTS2 '10520 TRACE NAME CONTEXT OFF'; Session altered. This feature was designed as part of Oracle's ongoing initiative to speed up the process of applying database patch sets and upgrades. If you insist on using this feature for anything else, immediately afterwards you should shuffle the papers around on your desk, put on your best innocent look, and pretend that nothing happened.