How to compile all INVALID objects in Oracle

There are five ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile > Best Approach

1. DBMS_DDL
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]


Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);


Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username
Name : Objects name

Example
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed.


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


Syntax
Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)

Example
SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed.


3. UTL_RECOMP
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.


Syntax
Exec UTL_RECOMP.RECOMP_SERIAL ();

Example
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();
PL/SQL procedure successfully completed.
Note: Required SYS user to run this package.


4. UTLRP.SQL
Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.


Syntax
Located: $ORACLE_HOME/rdbms/admin

Example

1. Start SQL*Plus: sqlplus "/ AS SYSDBA"
2. Run the utlrp.sql script, where Oracle_home is the Oracle home path: SQL> @Oracle_home/rdbms/admin/utlrp.sql

Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.


5. Best Approach is manually recompiling all Invalid Objects


Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql


Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER


Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql


Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql


Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql


Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql