Operations such as upgrades, patches and DDL changes can invalidate schema objects

The DBA_OBJECTS view can be used to identify and manually validate invalid objects using the following query.

select 'alter ' ||object_type||' '||owner||'.'||object_name||' '||' compile;'
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

OutPut:

alter TRIGGER HR.TMP$$_CUSTOMER_CHECKS0 compile;
alter SYNONYM PUBLIC.XDB_CONFIGURATION compile;
alter SYNONYM PUBLIC.XDB_DOM_HELPER compile;
alter SYNONYM PUBLIC.XDB_NAMESPACES compile;
alter SYNONYM PUBLIC.XDB_TOOLS compile;
alter SYNONYM PUBLIC.XDB_UTILITIES compile;
alter TRIGGER SCOTT.CRMUPDATED_CLEAR compile;

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

 select 'EXEC DBMS_DDL.alter_compile('''||object_type ||''','''||owner|| ''','''||object_name ||''');'
 FROM   dba_objects
 WHERE  status = 'INVALID'
 ORDER BY owner, object_type, object_name;

Output:-

EXEC DBMS_DDL.alter_compile('SYNONYM','PUBLIC','XDB_CONFIGURATION');
EXEC DBMS_DDL.alter_compile('SYNONYM','PUBLIC','XDB_DOM_HELPER');
EXEC DBMS_DDL.alter_compile('SYNONYM','PUBLIC','XDB_NAMESPACES');
EXEC DBMS_DDL.alter_compile('SYNONYM','PUBLIC','XDB_TOOLS');
EXEC DBMS_DDL.alter_compile('SYNONYM','PUBLIC','XDB_UTILITIES');
EXEC DBMS_DDL.alter_compile('TRIGGER','SCOTT','CRMUPDATED_CLEAR');

Note: This method is limited to PL/SQL objects, so it is not applicable for views.

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus

select 'exec DBMS_UTILITY.COMPILE_SCHEMA (' || object_type || ',FALSE, FALSE);' 
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Example output:-

exec DBMS_UTILITY.COMPILE_SCHEMA (SYNONYM,FALSE, FALSE);
exec DBMS_UTILITY.COMPILE_SCHEMA (SYNONYM,FALSE, FALSE);
exec DBMS_UTILITY.COMPILE_SCHEMA (SYNONYM,FALSE, FALSE);
exec DBMS_UTILITY.COMPILE_SCHEMA (SYNONYM,FALSE, FALSE);
 
exec DBMS_UTILITY.COMPILE_SCHEMA (SYNONYM,FALSE, FALSE);
exec DBMS_UTILITY.COMPILE_SCHEMA (TRIGGER,FALSE, FALSE);

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects. 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.

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

   PROCEDURE RECOMP_SERIAL(
     schema   IN   VARCHAR2    DEFAULT NULL,
     flags    IN   PLS_INTEGER DEFAULT 0);

  PROCEDURE RECOMP_PARALLEL(
     threads  IN   PLS_INTEGER DEFAULT NULL,
     schema   IN   VARCHAR2    DEFAULT NULL,
     flags    IN   PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below.

schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled. threads - The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value. flags - Used for internal diagnostics and testing only.

The following examples show how these procedures are used.

Schema level.

  EXEC UTL_RECOMP.recomp_serial('SCOTT');
  EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

Database level.

  EXEC UTL_RECOMP.recomp_serial();
  EXEC UTL_RECOMP.recomp_parallel(4);

Using job_queue_processes value.

  EXEC UTL_RECOMP.recomp_parallel();
  EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

There are a number of restrictions associated with the use of this package including:

- Ordered List ItemParallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete. - Ordered List ItemThe package must be run from SQL*Plus as the SYS user, or another user with SYSDBA. - Ordered List ItemThe package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid. - Ordered List ItemRunnig DDL operations at the same time as this package may result in deadlocks.

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

  0 - The level of parallelism is derived based on the CPU_COUNT parameter.
  1 - The recompilation is run serially, one object at a time.
  N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.