Recompile Invalid Objects

Objects need to recompile are:

VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY


There are five ways to recompile invalid objects in schemas.

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




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


Exec dbms_ddl.alter_compile ( type , schema, name);
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');




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


Exec dbms_utility.compile_schema ( schema,compile all)
SQL> exec dbms_utility.compile_schema('SCOTT');




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


Exec UTL_RECOMP.RECOMP_SERIAL ();
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();




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


Located: $ORACLE_HOME/rdbms/admin
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL




5. Manually Recompile


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: For 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