.
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
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
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.