How to recompile invalid Oracle database objects

Search for invalid objects
You can use the following method to compile invalid objects in your database based on the query results.

COLUMN object_name FORMAT A30 SELECT owner, object_type, object_name, STATUS FROM dba_objects WHERE STATUS = 'INVALID' ORDER BY owner, object_type, object_name;

1. manual compilation
If the number of invalid objects is small, you can compile these objects one by one.
For example:

ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE;

You can also use the dbms_ddl package for compiling (but only for PL/SQL objects, so you don't need it to compile views, etc ):

EXEC DBMS_DDL('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE'); EXEC DBMS_DDL('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE'); EXEC DBMS_DDL('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE'); EXEC DBMS_DDL('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION'); EXEC DBMS_DDL('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

2. General scripts
In some cases, you may have many invalid objects to compile. In this case, manual compilation is too inefficient.
In this case, you can generate a general script to compile the script.
The following script is used to query invalid package and package bodies and generate scripts for cheaper objects.
However, this method does not consider the dependency between Oracle Objects.

SET SERVEROUTPUT ON SIZE 1000000 BEGIN FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS recompile_order FROM dba_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY') AND STATUS != 'VALID' ORDER BY 4) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE'; ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE BODY'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || ' : ' || cur_rec.object_name); END; END LOOP; END; /

3. Use dbms_utility.compile_schema
With this package, all procedures, functions, packages, and triggers under the specified schema will be compiled.
You can use it in sqlplus, for example:
Exec dbms_utility.compile_schema (schema => 'Scott ');
4. Use utl_recomp
The utl_recomp package has two stored procedures: recomp_serial and recpmp_parallel.
From the name of the stored procedure, we can see that one is non-parallel and the other is parallel.
Parallel compilation is accelerated. The package is defined as follows:

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 );
Parameter usage:
Schema
-If it is null, all invalid objects in the database will be compiled.
Threads
-Degree of parallelism. If it is null, the value of job_queue_processes is used.
Generally, the value of threads is best matched with the number of CPUs to take advantage of parallelism.
Flags-
The diagnostic test parameters used in Oracle.
For example:

-- 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');

Limits on the use of the utl_recomp package

(1). Parallel Execution uses the Job Queue. When parallel compilation is run, all jobs are Diable until the compilation is complete.
(2) The package must run in sqlplus as sys or sysdba.
(3). utl_recomp depends on dbms_standard, dbms_job, dbms_random
(4). Executing DDL statements when running this package may lead to deadlocks.

4. utlrp. SQL and utlgp. SQL
The utlrp and utlgp scripts can be used to recompile all invalid objects in the database. we usually see this script in the Oracle Upgrade guide. Oracle strongly recommends compiling invalid objects by running this script after migration/upgrade/downgrade. the script is located in the $ ORACLE_HOME/rdbms/Admin directory. you can see utlrp. the SQL statement only calls utlgp. SQL, utlrp. SQL in the call utlgp. SQL will be passed to it as a parameter. The default value is 0. This parameter is actually a degree of parallelism, in fact utlgp. SQL calls utl_recomp.recomp_parallel:
0-set the degree of Parallelism Based on the cpu_count parameter.
1-compile an object in serial mode and compile one object at a time.
N-compile with n degree of parallelism.
The script must be run by sys or sysdba.