Using those techniques we were able to identify table which is involved in the DML responsible for ORA-600. But that's it. Table does not have any data corruption and even after rebuilding whole table and its index we were facing same error.
Then we started looking into the query, which is a simple MERGE statement. Below is the query using similar table:
SQL> MERGE INTO abc X
2 USING
3 (
4
SELECT 2 AS ID FROM dual
5 ) U
6 ON
7 (X.b = U.id)
8 WHEN MATCHED THEN UPDATE SET X.c = 0;
Interesting thing here is that if we modify the query and convert it into an UPDATE statement then query updates one record and does not throw any error.
Now we started thinking that it might not be related to database or data corruption but due to some sort of bug in processing of MERGE statement or structure of table or its indexes. But we were unable to find any thing relevant over that internet.
We started with changing structure of table like its keys, constraints, column datatype but ORA-00600 error keep reappearing on MERGE statement. Then we starteded altering/ dropping indexes. Here we got successes. This table has one composite index. This index was on two columns, first column has on ordering clause but second one has DESC order clause. Like this:
CREATE INDEX abc_ind ON abc (b, c DESC);
We we dropped this index, merge statement executed successfully. Now we knew that there is something wrong this combination of this index and merge statement. Because when we removed that DESC clause and recreated that index merge again executed successfully.
MERGE statement fails if base table has an composite index on columns involved in query and DESC order is used during index creation. But there is one more requirement of failure. That is leading column of index should be used in ON clause of MERGE. For example if there are three columns a,b and c in a table and merge condition is on column b and update clause is on column c then if composite index is on columns (b, c DESC) then MERGE will fail. But if index is on columns (a, c DESC) then MERGE won’t fail.
I got away by modifying that index and removing DESC clause, because there was really no requirement for that. We don't know how it get introduced in the first place. But it may be possible some where else we might actually need that type of index and again face similar issue.
Following are the reproduction steps:
SQL> DROP TABLE abc;
DROP TABLE abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE abc(
2 a
INT,
3 b INT,
4 c INT
5 );
Table created.
SQL> INSERT INTO abc VALUES (2,1,1);
1 row created.
SQL> INSERT INTO abc VALUES (1,4,2);
1 row created.
SQL> INSERT INTO abc VALUES (6,6,3);
1 row created.
SQL> INSERT INTO abc VALUES (1,1,4);
1 row created.
SQL> INSERT INTO abc VALUES (3,7,5);
1 row created.
SQL> INSERT INTO abc VALUES (1,2,5);
1 row created.
SQL> COMMIT;
Commit complete.
/*MERGE without any index.
RESULT:: SUCCESS */
SQL> MERGE INTO abc X
2 USING
3 (
4
SELECT 2 AS ID FROM dual
5 ) U
6 ON
7 (X.b = U.id)
8 WHEN MATCHED THEN UPDATE SET X.c = 0;
1 row merged.
SQL> ROLLBACK;
Rollback complete.
SQL>
CREATE INDEX abc_ind ON abc (b, c DESC);
Index created.
/*MERGE with composite index on
columns involved in ON clause and UPDATE clause of merge with ordering.
RESULT:: FAILURE*/
SQL> MERGE INTO abc X
2 USING
3 (
4
SELECT 2 AS ID FROM dual
5 ) U
6 ON
7 (X.b = U.id)
8 WHEN MATCHED THEN UPDATE SET X.c = 0;
MERGE INTO abc X
*
ERROR at line 1:
ORA-00600: internal error code,
arguments: [13013], [5001], [101263],
[17027069], [5], [17027069], [17],
[], [], [], [], []
SQL> DROP INDEX abc_ind;
Index dropped.
SQL>
CREATE INDEX abc_ind ON abc (b, c);
Index created.
/*MERGE with composite index on
columns involved in ON clause and UPDATE clause of merge but without ordering.
RESULT:: SUCCESS */
SQL> MERGE INTO abc X
2 USING
3 (
4
SELECT 2 AS ID FROM dual
5 ) U
6 ON
7 (X.b = U.id)
8 WHEN MATCHED THEN UPDATE SET X.c = 0;
1 row merged.
SQL> ROLLBACK;
Rollback complete.
SQL> DROP INDEX abc_ind;
Index dropped.
SQL>
CREATE INDEX abc_ind ON abc (a, c DESC);
Index created.
/*MERGE with composite index. But
leading column of index is not involved in ON clause. RESULT:: SUCCESS */
SQL> MERGE INTO abc X
2 USING
3 (
4
SELECT 2 AS ID FROM dual
5 ) U
6 ON
7 (X.b = U.id)
8 WHEN MATCHED THEN UPDATE SET X.c = 0;
1 row merged.
SQL> ROLLBACK;
Rollback complete.
SQL>
