Friday, February 16, 2007

Re-Compile Invalid Objects

When making changes to schema objects, Oracle invalidates all dependent objects. A procedure getEmpData() that references the employees table is invalidated when the employees table is altered.

When an invalid object is referenced, Oracle automatically recompiles it. This will take some time and it is not guaranteed that the compilation will succeed.

It is therefore strongly suggested to make sure all database objects are valid after changes have been applied (this includes patches from Oracle)!

The SQL below can be used to find all invalid objects in a database (connect with dba privileges):
column owner format a10 wrap
column object_type format a20 wrap
column object_name format a30
column status format a10
select owner, object_type, object_name, status
from dba_objects
where status != 'VALID'
order by owner, object_type, object_name

Compile all objects of a given schema:
exec dbms_utility.compile_schema('<schema name>')
or exec utl_recomp.recomp_parallel(schema => '<schema name>')

Compile all objects in the database:
start ?/rdbms/admin/utlrp.sql
or exec utl_recomp.recomp_parallel

Here is my post that show how to generate individual compile statements.

No comments: