Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause
compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable
time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects
in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several
methods for recompiling invalid schema objects.
?Identifying Invalid Objects
?The Manual Approach
?Custom Script
?DBMS_UTILITY.compile_schema
?UTL_RECOMP
?utlrp.sql and utlprp.sql
*************************************************************************************************************************************************
(1) Identifying Invalid Objects
*************************************************************************************************************************************************
The DBA_OBJECTS view can be used to identify invalid objects using the following query.
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;
set linesize 180
set pagesize 1000
column owner format a20
column object_type format a15
column status format a10
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;
OWNER OBJECT_TYPE OBJECT_NAME STATUS CREATED
--------------- --------------- ---------------------------------------- ---------- ------------
ABOQ FUNCTION GET_AUDIT_CNT INVALID 14-FEB-11
ABOQ FUNCTION GET_CHECK_CNT INVALID 14-FEB-11
ABOQ FUNCTION GET_RPT_CNT INVALID 14-FEB-11
ABOQ PROCEDURE P_BUILD_HOMEDATA INVALID 14-FEB-11
ABOQ PROCEDURE P_BUILD_HOMEDATA_DETAIL INVALID 14-FEB-11
ABOQ SYNONYM TB_ORG INVALID 14-FEB-11
ABOQ SYNONYM TB_USER INVALID 14-FEB-11
ABOQ VIEW T_ORG INVALID 14-FEB-11
ABOQ VIEW T_USER INVALID 14-FEB-11
ABOQ VIEW T_USER_ORG INVALID 14-FEB-11
ACC VIEW T_USER INVALID 10-OCT-12
ACC VIEW T_USER_ORG INVALID 10-OCT-12
ACC VIEW V_ACC_USER INVALID 20-DEC-12
ACC VIEW V_ROLE_USER INVALID 20-DEC-12
CAR VIEW AUTH_BANK_GROUP_MEMBER INVALID 08-JAN-13
CAR VIEW AUTH_USER_BANK_RIGHTS INVALID 08-JAN-13
CAR VIEW AUTH_USER_CURR_RIGHTS INVALID 08-JAN-13
CAR VIEW AUTH_USER_GROUP_MEMBER INVALID 08-JAN-13
With this information you can decide which of the following recompilation methods is suitable for you.
*************************************************************************************************************************************************
The Manual Approach
*************************************************************************************************************************************************
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.
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;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations.
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.
*************************************************************************************************************************************************
Custom Script
*************************************************************************************************************************************************
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects.
The following example identifies and recompiles invalid packages and package bodies.
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;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in.
It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
*************************************************************************************************************************************************
DBMS_UTILITY.compile_schema
*************************************************************************************************************************************************
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.
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
set linesize 180
set pagesize 1000
column owner format a20
column object_type format a15
column status format a10
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID' AND OWNER='UPRR'
ORDER BY owner, object_type, object_name;
OWNER OBJECT_TYPE OBJECT_NAME STATUS
-------------------- --------------- ------------------------------ ----------
UPRR VIEW EXTENT_BASE INVALID
UPRR VIEW REPORT_META_1104 INVALID
UPRR VIEW REPORT_META_FZ INVALID
UPRR VIEW REPORT_META_HN INVALID
UPRR VIEW REPORT_META_RH INVALID
UPRR VIEW V_AUTH_BANK_GROUP_MEMBER_CRMS INVALID
UPRR VIEW V_BASE_USER_EMAIL INVALID
UPRR VIEW V_BRN_FOR_NIIC INVALID
UPRR VIEW V_HOLIDAY INVALID
UPRR VIEW V_INST_AUTHOR_FOR_ABOQ INVALID
UPRR VIEW V_INST_AUTHOR_FOR_ABOQ_BAK INVALID
UPRR VIEW V_INST_AUTHOR_FOR_ACC INVALID
UPRR VIEW V_INST_AUTHOR_FOR_BOP INVALID
UPRR VIEW V_INST_AUTHOR_FOR_CFA2 INVALID
UPRR VIEW V_INST_AUTHOR_FOR_FAL INVALID
UPRR VIEW V_INST_AUTHOR_FOR_PFS INVALID
UPRR VIEW V_ROLES_BY_USER_FOR_NIIC INVALID
UPRR VIEW V_USER_AUTH_BANK_RELATION INVALID
UPRR VIEW V_USER_AUTH_BANK_RELATION_AMLM INVALID
UPRR VIEW V_USER_AUTH_BANK_RELATION_CAR INVALID
UPRR VIEW V_USER_AUTH_BANK_RELATION_IRR INVALID
UPRR VIEW V_USER_AUTH_BANK_RELATION_RCP INVALID
UPRR VIEW V_USER_LIST_CAS INVALID
UPRR VIEW V_USER_ROLE_FOR_AML INVALID
24 rows selected.
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'UPRR');
PL/SQL procedure successfully completed.
OWNER OBJECT_TYPE OBJECT_NAME STATUS
-------------------- --------------- ------------------------------ ----------
UPRR VIEW EXTENT_BASE INVALID
UPRR VIEW REPORT_META_1104 INVALID
UPRR VIEW REPORT_META_FZ INVALID
UPRR VIEW REPORT_META_HN INVALID
UPRR VIEW REPORT_META_RH INVALID
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'UPRR');
PL/SQL procedure successfully completed.
*****************************************************************************************************
set linesize 80
SELECT 'ALTER ' || object_type || ' "' || owner || '"."' || object_name || '" COMPILE BODY;'
FROM DBA_OBJECTS
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY owner, object_type, object_name;
'ALTER'||OBJECT_TYPE||'"'||OWNER||'"."'||OBJECT_NAME||'"COMPILEBODY;'
--------------------------------------------------------------------------------
ALTER PACKAGE BODY "DATACORE"."DM_FSD" COMPILE BODY;
set linesize 80
SELECT 'ALTER ' || object_type || ' "' || owner || '"."' || object_name || '" COMPILE;'
FROM DBA_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW')
AND status != 'VALID'
ORDER BY owner, object_type, object_name;
'ALTER'||OBJECT_TYPE||'"'||OWNER||'"."'||OBJECT_NAME||'"COMPILE;'
--------------------------------------------------------------------------------
ALTER FUNCTION "ABOQ"."GET_AUDIT_CNT" COMPILE;
ALTER FUNCTION "ABOQ"."GET_CHECK_CNT" COMPILE;
ALTER FUNCTION "ABOQ"."GET_RPT_CNT" COMPILE;
ALTER PROCEDURE "ABOQ"."P_BUILD_HOMEDATA" COMPILE;
ALTER PROCEDURE "ABOQ"."P_BUILD_HOMEDATA_DETAIL" COMPILE;
ALTER VIEW "ABOQ"."T_ORG" COMPILE;
ALTER VIEW "ABOQ"."T_USER" COMPILE;
ALTER VIEW "ABOQ"."T_USER_ORG" COMPILE;
ALTER VIEW "ACC"."T_USER" COMPILE;
ALTER VIEW "ACC"."T_USER_ORG" COMPILE;
ALTER VIEW "ACC"."V_ACC_USER" COMPILE;
ALTER VIEW "ACC"."V_ROLE_USER" COMPILE;
ALTER VIEW "CAR"."AUTH_BANK_GROUP_MEMBER" COMPILE;
ALTER VIEW "CAR"."AUTH_USER_BANK_RIGHTS" COMPILE;
ALTER VIEW "CAR"."AUTH_USER_CURR_RIGHTS" COMPILE;
ALTER VIEW "CAR"."AUTH_USER_GROUP_MEMBER" COMPILE;
ALTER VIEW "CAR"."AUTH_USER_OPER_RIGHTS" COMPILE;
ALTER VIEW "CAR"."AUTH_USER_REPORT_RIGHTS" COMPILE;
ALTER VIEW "CAR"."BASE_USER_EMAIL" COMPILE;
ALTER VIEW "CAR"."NEW_AUTH_BANK_RELATION_CELL" COMPILE;
ALTER VIEW "CAR"."NEW_U_BASE_INST_RELA" COMPILE;
ALTER VIEW "CFA"."V_UPRR_HOLIDAY" COMPILE;
ALTER VIEW "CFA"."V_USER_BUSS_TYPE_FROM_UPRR" COMPILE;
ALTER VIEW "CFA"."V_USER_RESOURCE_FROM_UPRR" COMPILE;
ALTER VIEW "CFA2"."T_USER" COMPILE;
ALTER VIEW "CFA2"."V_ROLE_USER" COMPILE;
ALTER VIEW "CFA2"."V_UPRR_HOLIDAY" COMPILE;
ALTER VIEW "CFA2"."V_USER_BUSS_TYPE_FROM_UPRR" COMPILE;
ALTER VIEW "CFA2"."V_USER_RESOURCE_FROM_UPRR" COMPILE;
ALTER PROCEDURE "CIOS"."SP_DELETE_T_BASE_CHANGES" COMPILE;
ALTER PROCEDURE "CIOS"."SP_FCC_UDL" COMPILE;
set linesize 80
SELECT 'ALTER ' || object_type || ' "' || owner || '"."' || object_name || '" COMPILE;'
FROM DBA_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW')
AND status != 'VALID'
AND owner = 'UPRR'
ORDER BY owner, object_type, object_name;
'ALTER'||OBJECT_TYPE||'"'||OWNER||'"."'||OBJECT_NAME||'"COMPILE;'
--------------------------------------------------------------------------------
ALTER VIEW "UPRR"."EXTENT_BASE" COMPILE;
ALTER VIEW "UPRR"."REPORT_META_1104" COMPILE;
ALTER VIEW "UPRR"."REPORT_META_FZ" COMPILE;
ALTER VIEW "UPRR"."REPORT_META_HN" COMPILE;
ALTER VIEW "UPRR"."REPORT_META_RH" COMPILE;
*****************************************************************************************************************************************
UTL_RECOMP
*****************************************************************************************************************************************
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:
?Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
?The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
?The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
?Runnig DDL operations at the same time as this package may result in deadlocks.
*****************************************************************************************************************************************
utlrp.sql and utlprp.sql
*****************************************************************************************************************************************
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.