How to compile all Oracle schema objects using dbms_utility.complie_schema

compile_schema is used to compile a whole schema which means you can use the procedure to re-compile procedures, functions, packages and triggers. This procedure in Oracle built-in called DBMS_UTILITY package.

The package should be run with caution as this will take a long time to execute – depending on the size and number of objects in your schema.

Precedure signature (Oracle 8x):

procedure compile_schema(schema varchar2);

Paramemters:

schema – The name of schema that you want to compile.

Oracle 11g Syntax:

procedure compile_schema(schema varchar2,

compile_all boolean default TRUE,

reuse_settings boolean default FALSE);

Paramemters:

schema – The name of schema that you want to compile.

compile_all – a boolean flag to indicate if all schema objects should be compiled even if they are INVALID

reuse_settings – a boolean flag to indicate if the sessions settings should be reused

Example:

To compile objects in SCOTT schema

exec dbms_utility.compile_schema(‘SCOTT’);

exec dbms_utility.compile_schema(‘SCOTT’,TRUE, TRUE);