INVALID OBJECTS

SOLUTION

An INVALID Object problem may arise due to:

    Due to any patch installation.
    Database/Application level up-gradation
    DDL Changes

These changes may not cause compilation failures as the objects will be re-validated 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 the database code.

 

1. Check for invalid objects in the database

The invalid object will nearly always appear on Service Management System (SMS) servers.  To check for invalid objects it is preferable to be connected to the database as the 'sysdba' user so you can have access to all the objects.

1.1 Connect on the SMS and connect to oracle

To connect to the database as 'sysdba' you have to change to the oracle user:

smf_oper$ su - oracle
Password:
oracle$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 7 12:31:13 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
SQL>

1.2 Check for invalid objects

Type the following query to obtain the list of invalid objects (the result obtained is an example):

SQL> set pagesize 200 linesize 132
SQL> SELECT owner, object_type, object_name, status
     FROM all_objects
     WHERE status = 'INVALID'
     ORDER BY owner;

OWNER                          OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------------------ ------------------- ------------------------------ -------
CCS_ADMIN                      TRIGGER             CCS_R_TABLE_TARIFF_MAP_DT      INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_EVENT_CHARGE_AD        INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_ACCT_TYPE_CHARGE_AD    INVALID
CCS_ADMIN                      TRIGGER             CCS_ACS_RES_LIM_MT3            INVALID
CCS_ADMIN                      TRIGGER             CCS_ACS_CUST_DEL1              INVALID
CCS_ADMIN                      TRIGGER             CCS_REWARDS_MT                 INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_REWARDS_AIU            INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_REWARDS_AD             INVALID
CCS_ADMIN                      TRIGGER             CCS_REWARDS_AAT                INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_REWARDS_BFR            INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_EVENT_CHARGE_UPK       INVALID
CCS_ADMIN                      TRIGGER             REP_CCS_REWARDS_UPK            INVALID
PUBLIC                         SYNONYM             BBD_PAYMENT                    INVALID
PUBLIC                         SYNONYM             CCS_BE_CDR_TAGS_V              INVALID
PUBLIC                         SYNONYM             UIS_CDR                        INVALID
PUBLIC                         SYNONYM             BBD_CONTEXT                    INVALID
PUBLIC                         SYNONYM             BBD_CONTEXT_HISTORY            INVALID
PUBLIC                         SYNONYM             BBD_ERROR_TRACE                INVALID
PUBLIC                         SYNONYM             BBD_TRACE                      INVALID
PUBLIC                         SYNONYM             BBD_TRANSACTION                INVALID
PUBLIC                         SYNONYM             BBD_TDR_VW                     INVALID

22 rows selected.

In the result above there are 12 invalid triggers and 9 synonyms.

2. Recompiling the invalid objects in the database

2.1 Creating the commands to recompile the invalid objects

To help recompile the invalid object, we are going to use a query that will fetch the invalid objects and create the individual SQL commands to recompile these objects:

SQL> SELECT CASE WHEN object_type = 'SYNONYM' AND owner = 'PUBLIC' THEN
    'alter ' || owner || ' ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    ELSE
    'alter ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || owner || '.' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    END "SQL_COMMANDS"
    FROM dba_objects
    WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SYNONYM')
    AND status = 'INVALID'
    ORDER BY DECODE(object_type, 'TRIGGER', '99', '00');

SQL_COMMANDS
--------------------------------------------------------------------------------------------------------------------------------------
alter PUBLIC SYNONYM BBD_PAYMENT COMPILE;
alter PUBLIC SYNONYM CCS_BE_CDR_TAGS_V COMPILE;
alter PUBLIC SYNONYM UIS_CDR COMPILE;
alter PUBLIC SYNONYM BBD_TDR_VW COMPILE;
alter PUBLIC SYNONYM BBD_CONTEXT_HISTORY COMPILE;
alter PUBLIC SYNONYM BBD_ERROR_TRACE COMPILE;
alter PUBLIC SYNONYM BBD_TRACE COMPILE;
alter PUBLIC SYNONYM BBD_TRANSACTION COMPILE;
alter PUBLIC SYNONYM BBD_CONTEXT COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_EVENT_CHARGE_AD COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_ACCT_TYPE_CHARGE_AD COMPILE;
alter TRIGGER CCS_ADMIN.CCS_ACS_RES_LIM_MT3 COMPILE;
alter TRIGGER CCS_ADMIN.CCS_ACS_CUST_DEL1 COMPILE;
alter TRIGGER CCS_ADMIN.CCS_REWARDS_MT COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_AIU COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_AD COMPILE;
alter TRIGGER CCS_ADMIN.CCS_REWARDS_AAT COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_BFR COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_UPK COMPILE;
alter TRIGGER CCS_ADMIN.REP_CCS_EVENT_CHARGE_UPK COMPILE;
alter TRIGGER CCS_ADMIN.CCS_R_TABLE_TARIFF_MAP_DT COMPILE;

21 rows selected.

SQL>

We now have all the commands to be able to recompile all the invalid objects.

The objects can also be recompiled using the following SQL command:

SQL> EXEC DBMS_UTILITY.compile_schema('schema_name');

Where 'schema_name' is the name of the schema whose invalid objects are to be compiled, for example:

SQL> EXEC DBMS_UTILITY.compile_schema('SMF');

2.2 Recompiling the invalid objects

Using the commands created in the previous step we will recompile the invalid objects:

SQL> alter PUBLIC SYNONYM BBD_PAYMENT COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM CCS_BE_CDR_TAGS_V COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM UIS_CDR COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_TDR_VW COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_CONTEXT_HISTORY COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_ERROR_TRACE COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_TRACE COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_TRANSACTION COMPILE;

Synonym altered.

SQL> alter PUBLIC SYNONYM BBD_CONTEXT COMPILE;

Synonym altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_EVENT_CHARGE_AD COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_ACCT_TYPE_CHARGE_AD COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.CCS_ACS_RES_LIM_MT3 COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.CCS_ACS_CUST_DEL1 COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.CCS_REWARDS_MT COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_AIU COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_AD COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.CCS_REWARDS_AAT COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_BFR COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_REWARDS_UPK COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.REP_CCS_EVENT_CHARGE_UPK COMPILE;

Trigger altered.

SQL> alter TRIGGER CCS_ADMIN.CCS_R_TABLE_TARIFF_MAP_DT COMPILE;

Trigger altered.

SQL> 

2.3 Final check

Now that all the invalid objects have been recompiled, make a last check.

First check for any hidden errors:

SQL> show error

No errors
-- AND

SQL> select * from dba_errors;


Then check for any remaining invalid objects.

SQL> set pagesize 200 linesize 132SQL> SELECT owner, object_type, object_name, status
     FROM all_objects
     WHERE status = 'INVALID'
     ORDER BY owner;

0 rows selected.

3. Errors

Sometimes when trying to recompile an invalid object you may encounter some errors during the compile:

 

For example:

SQL> alter PROCEDURE SYS.JOBSTATUSCHECK COMPILE;

Warning: Procedure altered with compilation errors.

If these cases happen, please open a new Service Request and ask for help.

Also see the utlrp.sql script, as another way to recompile invalid objects.