Recompiling the invalid objects using utlrp.sql and utlprp.sql

Generally, the utlrp.sql and utlprp.sql scripts will run after any changes to the database like an upgrade or patch. The location of the script will $ORACLE_HOME/rdbms/admin directory and the reason to run this script is to recompile the invalidate schema objects.

How to run these scripts ? 

The standard way to run as a script in sql prompt @$ORACLE_HOME/rdbms/admin/utlrp.sql. Also we can setup the level of parallelism by passing the command-line values as 0, 1, N.

0 – This will help to re-compile based on CPU_COUNT parameter.

1 – This will recompile the objects serially one at a time.

N – This will recompile the objects with “N” number of threads in parallel.

How to identify the Invalid Objects ?

By querying the dba_objects view as below, you can list all the invalid objects.

SELECT  owner,  object_type,  object_name,  status FROM   dba_objects  WHERE  status = 'INVALID';

Once if any invalid objects are identified, we can recompile using the below command based on type of object.

ALTER PACKAGE package_name COMPILE;

ALTER PACKAGE procedure_name COMPILE;

ALTER PACKAGE trigger_name COMPILE;

ALTER PACKAGE function_name COMPILE;

Using the DBMS_DDL package to recompile the invalid objects.

EXEC DBMS_DDL.alter_compile('PROCEDURE', 'schema_name', 'precedure_name');

EXEC DBMS_DDL.alter_compile('FUNCTION', 'schema_name', 'function_name');

EXEC DBMS_DDL.alter_compile('TRIGGER', 'schema_name', 'trigger_name');

Using DBMS_UTILITY package and compile_schema procedure to compile all procedures, functions, packages and triggers related to particular schema.

EXEC DBMS_UTILITY.compile_schema(schema => 'HR');

Using UTL_RECOMP package we can recompile the invalid objects using 2 procedures.

1. RECOMP_SERIAL – This procedure will allow to perform the compilation serially one at a time.

Example:

EXEC UTL_RECOMP.recomp_serial('HR');

2. RECOMP_PARALLEL – This procedure will allow the compilation parallely using the number of threads.

Example:

EXEC UTL_RECOMP.recomp_parallel(2, 'HR');