Identifying INVALID objects in Oracle Database

The DBA_OBJECTS view can be used to identify invalid objects using the following query:

SQL>COLUMN object_name FORMAT A30
SQL>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  
 ------------ ------------------- --------------------------------------- ------- ---  
 DMSYS    PACKAGE BODY    DBMS_DATA_MINING_INTERNAL        INVALID  
 DMSYS    PACKAGE BODY    DBMS_DM_UTIL              INVALID  
 DMSYS    PACKAGE BODY    DM_SEC_SYS               INVALID  
 OLAPSYS   PACKAGE       CWM2_OLAP_AW_AWUTIL           INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_AW_AWUTIL           INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_EXPORT            INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_MANAGER            INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_OLAPAPI_ENABLE        INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_PC_TRANSFORM         INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_UTILITY            INVALID  
 OLAPSYS   PACKAGE BODY    DBMS_AW_UTILITIES            INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 OLAPSYS   PACKAGE BODY    DBMS_ODM                INVALID  
 OLAPSYS   PACKAGE BODY    OLAPDIMVIEW               INVALID  
 OLAPSYS   PACKAGE BODY    OLAPFACTVIEW              INVALID  
 SYS     PACKAGE       DBMS_REPCAT_MIGRATION          INVALID  
 SYS     PACKAGE       LTADM                  INVALID  
 SYS     PACKAGE BODY    DBMS_LOGMNR_D              INVALID  
 SYS     PACKAGE BODY    DBMS_REGISTRY              INVALID  
 SYS     PACKAGE BODY    DBMS_REPCAT_MIGRATION          INVALID  
 SYS     PACKAGE BODY    DBMS_STREAMS_ADM_UTL_INVOK       INVALID  
 SYS     PACKAGE BODY    DBMS_STREAMS_AUTH            INVALID  
 SYS     PACKAGE BODY    DBMS_SUMREF_UTIL            INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 SYS     PACKAGE BODY    KUPM$MCP                INVALID  
 SYS     PACKAGE BODY    KUPW$WORKER               INVALID  
 SYS     PACKAGE BODY    LT                   INVALID  
 SYS     PACKAGE BODY    LTADM                  INVALID  
 SYS     PACKAGE BODY    LTDDL                  INVALID  
 SYS     PACKAGE BODY    LTDTRG                 INVALID  
 SYS     PACKAGE BODY    LTRIC                  INVALID  
 SYS     PACKAGE BODY    LTUTIL                 INVALID  
 SYS     PACKAGE BODY    OWM_BULK_LOAD_PKG            INVALID  
 SYS     PACKAGE BODY    OWM_DDL_PKG               INVALID  
 SYS     PACKAGE BODY    OWM_MIG_PKG               INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 SYS     PACKAGE BODY    OWM_MP_PKG               INVALID  
 SYS     PACKAGE BODY    UD_TRIGS                INVALID  
 SYS     PACKAGE BODY    UTL_FILE                INVALID  
 SYS     PACKAGE BODY    WM_DDL_UTIL               INVALID  
 XDB     PACKAGE BODY    DBMS_XMLPARSER             INVALID  
 XDB     PACKAGE BODY    DBMS_XSLPROCESSOR            INVALID  
 39 rows selected.  
 SQL>  

How to RECOMPILE INVALID objects in Oracle database?

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SYS');
EXEC UTL_RECOMP.recomp_parallel(4, 'SYS');

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

SQL> exec utl_recomp.recomp_serial('SYS');

PL/SQL procedure successfully completed.

SQL> exec utl_recomp.recomp_serial('XDB');

PL/SQL procedure successfully completed.

SQL> exce utl_recomp.recomp.serial('OLAPSYS')

PL/SQL procedure successfully completed.

SQL> exce utl_recomp.recomp_serial('DBSYS')

PL/SQL procedure successfully completed.

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.

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 above scripts must be run as the SYS user, or any other user with SYSDBA role.




Twitter : https://twitter.com/rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/
Facebook : https://www.facebook.com/rkushawaha