How to compile all database objects?

Instead of manually compiling procedures, functions, packages and triggers, it’s possible to compile the entire schema by using Oracle’s built in utility.

Use Oracle internal utility to compile objects DBMS_UTILITY.COMPILE_SCHEMA

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

DBMS_UTILITY.COMPILE_SCHEMA (
   schema      VARCHAR2,
   compile_all BOOLEAN DEFAULT TRUE);

After calling this procedure, you should select from view ALL_OBJECTS for items with status of INVALID to see if all objects were successfully compiled.

To see the errors associated with INVALID objects, you may use the Enterprise Manager command:

SHOW ERRORS

The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'TEST');

Instead you should only recompile invalid objects:

EXEC dbms_utility.compile_schema(user, false);

Leave a Reply

Your email address will not be published. Required fields are marked *