Oracle 12c using onDelete Trigger on Table with foreign key and onDelete cascade setNull throws ORA-00600: Internal Errorcode, Arguments: [13001]

Question

.

Hello swarm intelligence, here's the problem, I can't seem to solve without a little help from the StackOverflow crowd :)

Given this Oracle Database

• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
• Production PL/SQL Release 12.1.0.2.0 - Production
• "CORE 12.1.0.2.0 Production" TNS for Solaris: Version 12.1.0.2.0 -
• Production NLSRTL Version 12.1.0.2.0 - Production

And this DDL and data:

CREATE TABLE TR_TEST_HIST ( 
    AUDIT_EVENT_TIMESTAMP date  NOT NULL,
    AUDIT_EVENT_TYPE     char(1)  NOT NULL,
    ID                   number(19)  NOT NULL,
    BUSINESS_KEY         varchar2(255)  NOT NULL,
    LINKED_ID            number(19)  
 );

CREATE INDEX PK_TR_TEST_0 ON TR_TEST_HIST ( ID );

CREATE INDEX IDX_TR_TEST_0 ON TR_TEST_HIST ( LINKED_ID );

CREATE TABLE TR_TEST ( 
    ID                   number(19)  NOT NULL,
    BUSINESS_KEY         varchar2(255)  NOT NULL,
    LINKED_ID            number(19)  ,
    CONSTRAINT PK_TR_TEST PRIMARY KEY ( ID ) 
 );

CREATE INDEX IDX_TR_TEST ON TR_TEST ( LINKED_ID );

CREATE OR REPLACE TRIGGER TR_TEST_AUDIT_TRIGGER
                                BEFORE DELETE OR UPDATE ON TR_TEST
                                FOR EACH ROW
                              DECLARE
                                VAR_CHANGE_TYPE CHAR(1);
                              BEGIN
                                IF UPDATING THEN VAR_CHANGE_TYPE := 'U'; ELSE VAR_CHANGE_TYPE := 'D'; END IF;
                                INSERT INTO TR_TEST_HIST (AUDIT_EVENT_TIMESTAMP,
AUDIT_EVENT_TYPE,
BUSINESS_KEY,
ID,
LINKED_ID) VALUES (CURRENT_TIMESTAMP,
VAR_CHANGE_TYPE,
:OLD.BUSINESS_KEY,
:OLD.ID,
:OLD.LINKED_ID);
                              END TR_TEST_AUDIT_TRIGGER;

ALTER TABLE TR_TEST ADD CONSTRAINT FK_TR_TEST_LINKED FOREIGN KEY ( LINKED_ID ) REFERENCES TR_TEST( ID ) ON DELETE SET NULL;

INSERT INTO TR_TEST( ID, BUSINESS_KEY, LINKED_ID ) VALUES ( 4, 'entry_4', 3 ); 
INSERT INTO TR_TEST( ID, BUSINESS_KEY, LINKED_ID ) VALUES ( 3, 'entry_3', null ); 
INSERT INTO TR_TEST( ID, BUSINESS_KEY, LINKED_ID ) VALUES ( 1, 'entry_1', null ); 
INSERT INTO TR_TEST( ID, BUSINESS_KEY, LINKED_ID ) VALUES ( 2, 'entry_2', 1 ); 

When I run

DELETE FROM TR_TEST WHERE ID IN (1, 2);

statement execution fails with this error

[2019-09-10 18:01:51] [60000][600] ORA-00600: Interner Fehlercode, Argumente: [13001], [], [], [], [], [], [], [], [], [], [], [] [2019-09-10 18:01:51] java.lang.RuntimeException: Error : 600, Position : 12, Sql = DELETE FROM TR_TEST WHERE ID IN (1, 2), OriginalSql = DELETE FROM TR_TEST WHERE ID IN (1, 2), Error Msg = ORA-00600: Interner Fehlercode, Argumente: [13001], [], [], [], [], [], [], [], [], [], [], []'

For the error message to occur, it makes no difference, if the audit trigger is BEFORE DELETE or AFTER DELETE.

But deleting everything one after another works and fills the audit table correctly

DELETE FROM TR_TEST WHERE ID IN (1);
DELETE FROM TR_TEST WHERE ID IN (2);

As well as deleting everything without WHERE clause, which also fills the audit table correctly

DELETE FROM TR_TEST;

The audit triggers also work, if one changes the foreign keys cascade rule to doNothing, but that is not the wanted behavior here.

Am I doing something wrong, or is this a real Oracle bug? Do you know any workaround?

Thanks for your help, Marius

Answer 1

I have tested this on my Oracle XE 18c and have reproduced the problem. There's good news and bad news. The good news is I think I know what causes the error. The bad news is you're not going to like it.

The clue is in the time it takes for the trigger to hurl ORA-00600. It's a long time. This is often an indicator of recursion or a deadlock timeout. And that's what I think is happening here: when you delete a record the ON DELETE SET NULL clause executes an update. That causes the trigger to fire again (because it fires before delete or update), which executes another update and fires the trigger, and so on. Eventually the transaction fails with ORA-00600. If we drop that foreign key constraint not only does the delete succeed it finishes like that!. No time at all.

So it's definitely the ON DELETE SET NULL clause which is the problem. There's just something about deleting multiple rows in one statement which causes Oracle to blow a fuse. This shouldn't make a difference when we can delete each row individually but it does. That's what makes it a bug.

Anyway, at least you have a reproducible test case: Support likes those.