I often compile invalid objects with the sys.utl_recomp package. It is an easy way to pick up any invalid objects, but there are faster and smarter ways to compile invalid objects – if you know ahead of time what will be going invalid. How would you be in a place to do this? Consider a scripted database deploy. It can be written well in advance of the deploy to production systems, and as it is run in different databases, you can easily see what goes invalid.
The SQL below will write DDL to compile any invalid objects. In addition to running it to compile the currently invalid objects, you can copy this set of DDL into your deploy scripts.
SELECT decode(object_type,’PACKAGE BODY’,’alter package ‘||owner||’.’||object_name||’ compile body;’,
‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’) cmd
FROM all_objects WHERE object_type IN (‘FUNCTION’,’JAVA SOURCE’,’PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’SYNONYM’,’TRIGGER’,’TYPE’,’TYPE BODY’,’VIEW’)
AND status = ‘INVALID’;
Incorporating the compile commands directly into your deploy scripts has several advantages:
- Move high priority objects to the top of the list, I put triggers first, then views, as often the views are used by the PL/SQL objects. Synonyms are usually fine to go last.
- Move compile commands to immediately after the objects become invalid. This requires some knowledge about what is causing the objects to become invalid. This is especially helpful if there are many changes in a deploy and you have a way to run parallel streams of changes. Allowing the compiles to happen in each stream as the objects are invalidated will significantly lower the disruption to running sessions.
With a bit of imagination, you can use the SQL above as a starting point for other actions. For example, if you want to recompile all triggers for user SCOTT, just change the SQL where clauses.
One additional note, I used all_objects in the SQL to allow any user to run this, but if you wish to use dba_objects, that works also.