How to find and Recompile Invalid Objects in Oracle

 From time to time we will need to recomplie database objects. Among the many resons why objects become invalid are : upgrades, DDL changes, patches.

Although invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime. So in this article we will go thru some methods i normally use to recompile invalid objects.

Using the dba_objects table

We will start by using the dba_objects table to identify our invalid objects and we will build a query on top of those invalid objects so  the output will generate the sql syntax to recompile invalid objects. - this method is good when you have a small number of invalid objects, maybe after a DDL change.

select 'alter ' || d1.object_type || ' ' || d1.owner || '.' ||
       d1.object_name || ' ' || ' compile;'
  FROM dba_objects d1
 WHERE d1.status = 'INVALID'
   and d1.owner != 'PUBLIC'
union
select 'alter ' || d2.owner || ' ' || d2.object_type || ' ' ||
       d2.object_name || ' ' || ' compile;'
  FROM dba_objects d2
 WHERE d2.status = 'INVALID'
   and d2.owner = 'PUBLIC';

The output of this script will be some thing like this: Just copy and paste the output to recomplie.

Also another method is using the EXEC DBMS_DDL package to perform the recompilations.

• views and synonyms will not be recomplied using this method.

SELECT 'EXEC DBMS_DDL.alter_compile(''' || object_type || ''',''' || owner ||
       ''',''' || object_name || ''');'
  FROM dba_objects
 WHERE status = 'INVALID'
   and object_type not in ('VIEW', 'SYNONYM')
 ORDER BY owner, object_type, object_name;

The same as with the previous script you will need to copy and paste the output in order to recomplie the invalid objects.

Last method i use is using the UTL_RECOMP package.

I use it after a major version upgrade. The UTL_RECOMP package contains two procedures used to recompile invalid objects. To do it at Schema Level

• Single threaded

EXEC UTL_RECOMP.recomp_serial('schema name');

• Is executed in Paralell(you set the number of threads to be used)

EXEC UTL_RECOMP.recomp_parallel(2, 'schema name');

To do at Database Level

• Single threaded

EXEC UTL_RECOMP.recomp_serial();

• Is executed in Paralell(you set the number of threads to be used)

EXEC UTL_RECOMP.recomp_parallel(2);