6 Replies Latest reply: Dec 28, 2012 1:08 PM by rcc50886 RSS

    Object Invalidation in Oracle 11g R2

    900086
      Hi All,

      I found a distinct behaviour in oracle 11gR2 which is not even available in previous releases . Let me explain with an example.

      --creating a small table
      create table TEMPSAMPLE (COL1 VARCHAR2(10),COL2 VARCHAR2(10),COL3 VARCHAR2(15),COL4 VARCHAR2(15));

      -- Now Creating an Primary key index for table TEMPSAMPLE

      ALTER TABLE TEMPSAMPLE ADD CONSTRAINT PKTEMPSAMPLE PRIMARY KEY (COL1,COL2);

      ---CREATING A VIEW ON THE ABOVE TABLE
      CREATE OR REPLACE VIEW VWTEMPSAMPLE AS
      SELECT * FROM TEMPSAMPLE;

      -- CREATING A PACKAGE WHICH USES TEMPSAMPLE AND VWTEMPSAMPLE OBJECTS.

      CREATE OR REPLACE PACKAGE PKGSAMP AS
      VAL1 TEMPSAMPLE.COL1%TYPE;
      VAL2 CONSTANT TEMPSAMPLE.COL1%TYPE:='11';
      PROCEDURE VERIFYSAMP(INVAL IN NUMBER);

      END PKGSAMP;
      /
      CREATE OR REPLACE PACKAGE BODY PKGSAMP IS
      PROCEDURE VERIFYSAMP(INVAL IN NUMBER)
      AS
      VAL1 TEMPSAMPLE.COL1%TYPE;
      VAL2 CONSTANT TEMPSAMPLE.COL1%TYPE:='11';
      BEGIN
      VAL1:='RAVI';
      FOR RC IN (SELECT * FROM VWTEMPSAMPLE)
      LOOP
      DBMS_OUTPUT.PUT_LINE('COL1 '||RC.COL1);
      DBMS_OUTPUT.PUT_LINE('COL2 '||RC.COL2);
      DBMS_OUTPUT.PUT_LINE('COL3 '||RC.COL3);
      END LOOP;
      INSERT INTO TEMPSAMPLE VALUES('REC05','RAVI','EEE','CK');
      DELETE FROM TEMPSAMPLE WHERE COL1='RECO1';
      UPDATE TEMPSAMPLE SET COL4='CKR' WHERE COL1='RECO2';
      DBMS_OUTPUT.PUT_LINE('VALUE IS '||INVAL);
      DBMS_OUTPUT.PUT_LINE('VALUE IS '||VAL1);
      END VERIFYSAMP;
      END PKGSAMP;

      --CREATING A PACKAGE PKGSAMP2 WHICH USES TEMPSAMPLE TABLE ITSELF

      CREATE OR REPLACE PACKAGE PKGSAMP2 AS
      VAL1 TEMPSAMPLE.COL1%TYPE;
      VAL2 CONSTANT TEMPSAMPLE.COL1%TYPE:='11';
      PROCEDURE VERIFYSAMP(INVAL IN NUMBER);

      END PKGSAMP2;
      /
      CREATE OR REPLACE PACKAGE BODY PKGSAMP2 IS
      PROCEDURE VERIFYSAMP(INVAL IN NUMBER)
      AS
      VAL1 TEMPSAMPLE.COL1%TYPE;
      VAL2 CONSTANT TEMPSAMPLE.COL1%TYPE:='11';
      BEGIN
      VAL1:='RAVI';
      FOR RC IN (SELECT * FROM TEMPSAMPLE)
      LOOP
      DBMS_OUTPUT.PUT_LINE('COL1 '||RC.COL1);
      DBMS_OUTPUT.PUT_LINE('COL2 '||RC.COL2);
      DBMS_OUTPUT.PUT_LINE('COL3 '||RC.COL3);
      END LOOP;
      INSERT INTO TEMPSAMPLE VALUES('REC05','RAVI','EEE','CK');
      DELETE FROM TEMPSAMPLE WHERE COL1='RECO1';
      UPDATE TEMPSAMPLE SET COL4='CKR' WHERE COL1='RECO2';
      DBMS_OUTPUT.PUT_LINE('VALUE IS '||INVAL);
      DBMS_OUTPUT.PUT_LINE('VALUE IS '||VAL1);
      END VERIFYSAMP;
      END PKGSAMP2;

      -- OBJECT STATUS OF PACKAGES
      SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN ('PKGSAMP','PKGSAMP2','VWTEMPSAMPLE');

      OBJECT_NAME   OBJECT_TYPE  STATUS*
      --------------------------------
      VWTEMPSAMPLE     VIEW     VALID
      PKGSAMP2     PACKAGE BODY     VALID
      PKGSAMP2     PACKAGE     VALID
      PKGSAMP     PACKAGE BODY     VALID
      PKGSAMP     PACKAGE     VALID

      Alter table TEMPSAMPLE DISABLE constraint PKTEMPSAMPLE KEEP INDEX;

      DROP INDEX PKTEMPSAMPLE;

      --OBJECT STATUS OF PACKAGES AFTER DROPPING INDEX

      SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN ('PKGSAMP','PKGSAMP2','VWTEMPSAMPLE');

      OBJECT_NAME   OBJECT_TYPE  STATUS*
      --------------------------------
      VWTEMPSAMPLE     VIEW     INVALID
      PKGSAMP2     PACKAGE BODY     VALID
      PKGSAMP2     PACKAGE     VALID
      PKGSAMP      PACKAGE BODY     INVALID
      PKGSAMP     PACKAGE     VALID


      Alter table TEMPSAMPLE ENABLE constraint PKTEMPSAMPLE;



      As per the above process, if we observe that drop of index on a table arel lead to invalidation of view which* depends on that table  and all the objects which uses this view will also get invalidated.*

      The above invalidation is being occurred only in Oracle 11g R2, due to which we are facing the issue in our application.

      We got a procedure where we disable a constraint , drop a index and process the insertion/updation into the tables. After successfull insertion/updation, at finally we are enabling the constraint.

      This worked fine with previous releases of oracle 11g R2 , where as we recently migrated to 10g R2 which was leading to invalidation of all packages which uses the view , and in the application which uses previous db sessions are unable to access the invalidated package and raising an exception.

      Please provide the solution if any possible