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