Compile full schema in Oracle

To compile an entire schema in Oracle, there are two utilities provided by Oracle.

• DBMS_UTILITY.COMPILE_SCHEMA
• Two procedures in UTL_RECOMP

DBMS_UTILITY.COMPILE_SCHEMA
This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges).

I am using Oracle 10g (10.2.0.3.0) for the following examples.

Syntax:
DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN);

Example (from SQL *Plus):
EXEC DBMS_UTILITY.COMPILE_SCHMEA( schema => 'SCOTT', compile_all => FALSE);

For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter):

EXEC DBMS_UTILITY.COMPILE_SCHMEA( schema => 'SCOTT');

UTL_RECOMP Package
This package has been introduced from Oracle 10g Release 2, which is specially designed for recompiling invalid objects. It recompiles PLSQL modules, Java procedures etc. Only a SYSDBA account can run the subprograms in this package. But always a DBA can grant EXECUTE privilege to your schema (or PUBLIC).

There are two ways in which invalid objects are recompiled if you choose to use this package.

1. Sequentially
2. Parallelly

By using the subprogram RECOMP_SERIAL for compiling invalid objects sequentially one by one. The syntax of the subprogram is as follows:

UTL_RECOMP.RECOMP_SERIAL( schema VARCHAR2, flags BINARY_INTEGER);

Simply call the program as follows:

EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');

The subprogram RECOMP_PARALLEL is available within this package UTL_RECOMP to enable multiple threads for recompiling invalid objects. The syntax of the procedure is as follows:

UTL_RECOMP.RECOMP_PARALLEL (threads BINARY_INTEGER, schema VARCHAR2, flags BINARY_INTEGER);

Note: By using more number of threads the recompiling could be slow taken to consideration the I/O intensive nature of recompiling invalid objects. There is no optimized number of threads to be used while recompiling, but as a rule of thumb use minimum number of threads as possible.

Example of usage of RECOMP_PARALLEL procedure:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');

Coming soon: Article on comparison of these procedures.