Invalid objects in oracle

Hi Friends, I am with a new topic today   “checking Invalid object and compiling “

This is very Important things to check invalid objects and compile them .

If you are going to apply patch , upgrade database or Application version , migration activity  or any type of alteration in your database , invalid objects will create .

I would always recommend before applying patch or upgrade, migrate activity take count of Invalid objects and also take a backup for same .

Check Invalid objects count ----

select OWNER,count (OBJECT_NAME) from dba_objects where status='INVALID'  group by OWNER;

Take backup of Invalid objects ----

Create table Invalid_date  as select * from dba_objects where status =’INVALID’;

Compile of Invalid objects ---------

cd $ORACLE_HOME/rdbms/admin

You wil get one script called utlrp.sql

Just run at sql prompt

Connect to sqlplus  as -----

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 17 19:44:28 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@ utlrp.sql

It will compile these invalid objects , you can monitor also and check how many of compile here .

select OBJECT_NAME,CREATED,status,OWNER,EDITION_NAME,object_type from dba_objects where OBJECT_NAME in ('ONT_FEEDBACK_PKG','EGO_DUMMY_SUBSCR_PKG','BOM_TEST_SUBSCR_PKG');

OBJECT_NAM CREATED   STATUSOWNER   EDITION_NAME     OBJECT_TYPE

EGO_DUMMY_ 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE

SUBSCR_PKG

EGO_DUMMY_ 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE BODY

SUBSCR_PKG

BOM_TEST_S 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE

UBSCR_PKG

BOM_TEST_S 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE BODY

UBSCR_PKG

Manual compile database objects --------------

Ø  ALTER PACKAGE apps.BOM_TEST_UBSCR_PKG  COMPILE PACKAGE;

Ø  ALTER PACKAGE apps.BOM_TEST_S_UBSCR_PKG  COMPILE BODY;

If any error comes , we can find out by below command

Show error apps.BOM_TEST_UBSCR_PKG

It  will show the exact compilation error  and accordingly we can resolve it .

Dear Readers , Please let me know if any doubt or any issue or need to clarify any thing related to this post about invalid objects and compiling them . comment here , will reply you .