This discussion is archived
6 Replies Latest reply: Dec 28, 2012 11:08 AM by rcc50886 RSS

Object Invalidation in Oracle 11g R2

900086 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points