Unique Index Result Error with Ref Cursor on Oracle 11g but Not on 10g

6 06 2011

June 6, 2011

You might expect to see some differences when upgrading from one release version of Oracle Database to another release version (edit June 6, 2011: see this article for expected changes when upgrading from 10g to 11g).  For instance, you might find that Oracle Database 11.1.0.6 deadlock when Oracle Database 10.2.0.5 and below do not.  You might find that Oracle Database 11.1.0.6 and above throw error messages when ROWNUM is used when Oracle Database 10.2.0.5 and below do not throw error messages.  You might find that the following SQL statement (based on the contents of this article) returns different values for the SYS user depending on the Oracle Database release version:

SELECT SYS_CONTEXT('USERENV', 'SESSIONID') USERENV FROM DUAL; 

For example, on Oracle Database 11.2.0.2 when SYS connects as SYSDBA, you will see something like this for the output of that SQL statement:

USERENV
----------
4294967295

If the O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE on 11.2.0.2, and the SYS user connects without specifying AS SYSDBA, you might see something like this for the output of that SQL statement:

USERENV
-------
344841

If the SYS user connects on Oracle Database 9i, you will probably see something like this for the output of that SQL statement:

USERENV
-------
      0 

The above are all minor changes in bahavior.  If you are brave, go ahead and blindly upgrade to a new version of Oracle Database.  🙂

While browsing a couple of web forums I found another example of changed results caused by upgrading from Oracle Database 10g (possibly 10.2.0.4 or lower) to 11g (not stated, but possibly 11.2.0.2).  To be kind, my PL/SQL coding abilities are rusty, but I think that I follow the logic in that forum thread.  Let’s see if we are able to reproduce the problem with a couple of slight modifications.  First, let’s create a table with three rows:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  ORDER_ID VARCHAR2(20),
  PROCESSED NUMBER);

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Now create a PL/SQL package with an embedded COMMIT (as stated in the forum thread linked to above, this embedded COMMIT is included because this example is from a stateless web application):

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR; 

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Hopefully, I haven’t lost anyone yet, in the above: select a row, return the row as a ref cursor, and then update the row to show that the row has been processed.  Now let’s process the three orders using three anonymous PL/SQL blocks (it is not necessary to use three anonymous PL/SQL blocks, but this was done to verify that the problem happens on multiple executions also):

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/ 

The output from Oracle Database 11.2.0.2 looks like this:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.
 

The above worked as expected, the anonymous PL/SQL blocks output TESTING, TESTING2, and TESTING3.  Not particularly useful as written, but it executes and outputs the expected information.

Let’s create a non-unique index on the ORDER_ID column and repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE INDEX IND_T1 ON T1(ORDER_ID); 

The output from Oracle Database 11.2.0.2 showing the anonymous PL/SQL:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

The above test seemed to work as expected… TESTING, TESTING2, TESTING3.  Let’s create a primary key constraint on the ORDER_ID column, which will use the existing IND_T1 index to enforce the primary key constraint, then we will repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 ADD PRIMARY KEY(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Nothing unusual yet – you are probably wondering about the point of this article by now, almost there.  Let’s create a unique function based index on UPPER(ORDER_ID) and then repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID));

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion of the output:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1
IND_T1_FB

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Still nothing unusual yet.  Let’s try one more time, removing the primary key constraint, dropping the two indexes, and creating a single unique index on the ORDER_ID column (in case you are wondering, with just the function-based unique index on the table there were no problems):

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 DROP PRIMARY KEY;
DROP INDEX IND_T1;
DROP INDEX IND_T1_FB;
CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 (and 10.2.0.5):

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

Note that this time the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Oracle Database 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 all exhibit this unexpected behavior when a unique index is present on table T1’s ORDER_ID column. 

Oracle Database 10.2.0.4 does not exhibit this problem.  Below is the output from Oracle Database 10.2.0.4 for the last portion of the script:

SQL> CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

Index created.

SQL>
SQL> SELECT
  2    BANNER
  3  FROM
  4    V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Well, that is a little interesting.  Let’s try another test – remember that we had no trouble with the unique function-based index.  Let’s quickly revisit that example.  First, reset the T1 test table, drop the unique index, and then recreate the unique function-based index:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

DROP INDEX IND_T1_UNIQUE;
CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID)); 

Now let’s modify the package so that it will likely use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Let’s retry the anonymous PL/SQL blocks:

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

The output of the anonymous PL/SQL blocks:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed. 

Note that the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Let’s try fixing the package so that it will not use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT /*+ NO_INDEX(T1 IND_T1_FB) */
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Now, reset the table’s rows:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Finally, the output from the anonymous PL/SQL blocks on Oracle Database 11.2.0.2:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed. 

Testing 1, 2, 3.  I think that I just fixed a problem by using a hint… although we could have avoided the problem by creating a non-unique normal index with a primary key constraint, or by creating a non-unique index with a second unique function-based index to enforce the uniqueness in the column.

—

Added June 6, 2011:

The linked forum thread suggests that the problem might be identified in Metalink (MOS) Bug 10425196: “PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5”.  I was able to reproduce the problem in Oracle Database 10.2.0.5 on the Windows platform.  Also, note in the above tests that it is not necessarily the presence of a primary key index on the table that could cause the problem – as shown above, the problem is apparently caused by cases where a unique index is used to retrieve the rows for the ref cursor.

« »


Actions

Information

7 responses

6 06 2011
Dom Brooks (09:38:18) :

Very worrying.

When you consider other evidence such as this – http://jonathanlewis.wordpress.com/2011/03/20/upgrade-whoa/ – it’s hard not to come to the conclusion that core developers have been somewhat careless in and around the read consistency mechanism in recent years.

Reply
6 06 2011
Dom Brooks (09:51:38) :

It’s interesting that it’s in 10.2.0.5 that this first crops up.

I believe that that is the version when a commit inside a loop driven by a select for update started to raise an ora-1002 fetch out of sequence (a good change to protect against a flawed coding habit).

Reply
6 06 2011
Charles Hooper (10:05:24) :

Dom,

Thanks for providing the link to Jonathan’s article. I remembered reading a quote somewhere, but I could not remember where:

“It doesn’t really matter whether you think the old behaviour or the new behaviour is correct – the problem is that the behaviour has changed in a way that could silently produce unexpected results.”

Are you able to confirm that you see the problem in 10.2.0.5. I tested 10.2.0.5 in 64 bit Windows and was able to reproduce the problem. I then tested 10.2.0.4 in 32 bit Windows and could not reproduce the problem. The MOS (Metalink) Bug article states that the problem is not present in 10.2.0.5 on one of the Unix platforms (Solaris), but I am curious to know if the problem is found only in the Windows release of 10.2.0.5 (it was released after most of the Linux/Unix platforms and included additional bug fixes).

Reply
7 06 2011
Houri Mohamed (02:16:51) :

Dom,

I am not sure that the ora-1002 fetch out of sequence started to manifest itselve since 10.2.0.5.

I did remember reading an oracle documentation for Oracle 7 which was speaking about the error we will have when commiting inside a loop driven by a select for update.

Mohamed Houri

Reply
7 06 2011
Dom Brooks (04:20:21) :

Hi Mohamed,

I’m just doing an upgrade from 9.2.0.8 to 11.2.0.2 and a whole rack of PLSQL code doing exactly this was just revealed, thanks to this behaviour change (thank goodness). It may be that as far back as 7 that the behaviour was the same, I can’t remember now. Certainly in 9.2.0.8 the commit just releases the locks.

I can’t now seem to find the metalink note that I had previously regarding this change being explicitly implemented in 10.2.0.5.

Cheers,
Dominic

Reply
7 06 2011
Select For Update fetching across commit « OraStory (05:45:35) :

[…] was another one yesterday from Charles Hooper that definitely must be a bug because it’s related to whether a unique index is used as the […]

Reply
7 06 2011
Charles Hooper (11:31:09) :

I had wondered if the reason why this is working with a non-unique index and a full table scan, but not a unique index is the result of Oracle Database restarting the transaction, as described here in Tom Kyte’s “Expert Oracle Database Architecture” book:
http://books.google.com/books?id=HPJDlGmecwcC&pg=PA263
A before update trigger on table T1indicated that the updated only happened once, and it happened before the PL/SQL route output “Processing is complete” (that was expected).

For fun I enabled a 10046 extended SQL trace and a 10200 consistent gets trace. Unless I am reading the consistent gets trace incorrectly (that is definitely possible), it does not appear that Oracle Database builds a consistent read copy of the table block when the unique index is used to select the rows, while it does build a consistent read copy of the table block when a non-unique index is used to select the rows:

Unique Index Retrieve:

ktrgtc2(): started for block <0x0007 : 0x01d9983b> objd: 0x00015fb1
  env [0x000000001620DFCC]: (scn: 0x0000.52b9a13b  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a05f  flg: 0x00000661)
ktrexc(): returning 2 on:  0000000016780288  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0007 : 0x01d9983b> objd: 0x00015fb1
ktrgtc2(): started for block <0x0007 : 0x01d99834> objd: 0x00015fb0
  env [0x000000001620DFCC]: (scn: 0x0000.52b9a13b  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.52b9a13d  ma-scn: 0x0000.52b9a05f  flg: 0x00000662)
ktrexc(): returning 9 on:  0000000016780288  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0007 : 0x01d99834> objd: 0x00015fb0
FETCH #371953016:c=0,e=143,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=1,plh=1735687405,tim=1978249455074
STAT #371953016 id=1 cnt=0 pid=0 pos=1 obj=90032 op='TABLE ACCESS BY INDEX ROWID T1 (cr=2 pr=0 pw=0 time=137 us cost=1 size=25 card=1)'
STAT #371953016 id=2 cnt=1 pid=1 pos=1 obj=90033 op='INDEX UNIQUE SCAN IND_T1_U (cr=1 pr=0 pw=0 time=68 us cost=0 size=0 card=1)'

Non-Unique Index

ktrgtc2(): started for block <0x0007 : 0x01d9983b> objd: 0x00015fb7
  env [0x00000000198CBF5C]: (scn: 0x0000.52b9a528  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a468  flg: 0x00000661)
ktrexc(): returning 8 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0007 : 0x01d9983b> objd: 0x00015fb7
ktrget2(): started for block  <0x0007 : 0x01d99833> objd: 0x00015fb6
env [0x00000000198CBF5C]: (scn: 0x0000.52b9a528  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a468  flg: 0x00000661)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
kcbchg updating CR fields for 0x00000001D1F873F8, 31037491; 0:52b9a528
kcbchg new CR fields for 0x00000001D1F873F8, 31037491; 0:52b9a528
ktrgcm(): completed for block  <0x0007 : 0x01d99833> objd: 0x00015fb6
ktrget2(): completed for  block <0x0007 : 0x01d99833> objd: 0x00015fb6
FETCH #452951904:c=0,e=196,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=634656657,tim=1979147455414
ktrget2(): started for block  <0x0007 : 0x01d9983b> objd: 0x00015fb7
env [0x00000000198CBF5C]: (scn: 0x0000.52b9a528  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a468  flg: 0x00000660)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d9983b> objd: 0x00015fb7
ktrget2(): completed for  block <0x0007 : 0x01d9983b> objd: 0x00015fb7
FETCH #452951904:c=0,e=82,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=634656657,tim=1979147455578
STAT #452951904 id=1 cnt=1 pid=0 pos=1 obj=90038 op='TABLE ACCESS BY INDEX ROWID T1 (cr=4 pr=0 pw=0 time=190 us cost=1 size=25 card=1)'
STAT #452951904 id=2 cnt=1 pid=1 pos=1 obj=90039 op='INDEX RANGE SCAN IND_T1 (cr=2 pr=0 pw=0 time=150 us cost=1 size=0 card=1)'

Full Table Retrieve:

ktrget2(): started for block  <0x0007 : 0x01d99833> objd: 0x00015fb2
env [0x00000000198B970C]: (scn: 0x0000.52b9a294  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a1cf  flg: 0x00000661)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d99833> objd: 0x00015fb2
ktrget2(): completed for  block <0x0007 : 0x01d99833> objd: 0x00015fb2
ktrget2(): started for block  <0x0007 : 0x01d99834> objd: 0x00015fb2
env [0x00000000198B970C]: (scn: 0x0000.52b9a294  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a1cf  flg: 0x00000660)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d99834> objd: 0x00015fb2
ktrget2(): completed for  block <0x0007 : 0x01d99834> objd: 0x00015fb2
ktrget2(): started for block  <0x0007 : 0x01d99835> objd: 0x00015fb2
env [0x00000000198B970C]: (scn: 0x0000.52b9a294  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a1cf  flg: 0x00000660)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d99835> objd: 0x00015fb2
ktrget2(): completed for  block <0x0007 : 0x01d99835> objd: 0x00015fb2
ktrget2(): started for block  <0x0007 : 0x01d99836> objd: 0x00015fb2
env [0x00000000198B970C]: (scn: 0x0000.52b9a294  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a1cf  flg: 0x00000660)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d99836> objd: 0x00015fb2
ktrget2(): completed for  block <0x0007 : 0x01d99836> objd: 0x00015fb2
ktrget2(): started for block  <0x0007 : 0x01d99837> objd: 0x00015fb2
env [0x00000000198B970C]: (scn: 0x0000.52b9a294  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.52b9a1cf  flg: 0x00000660)
ktrexf(): returning 9 on:  000000001F2C8E98  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0007 : 0x01d99837> objd: 0x00015fb2
ktrget2(): completed for  block <0x0007 : 0x01d99837> objd: 0x00015fb2

Maybe someone can explain why a consistent read copy of the table block was not created when a unique index was used?

Reply

Leave a reply to Dom Brooks Cancel reply