Given is a SQL Server connected to an ORACLE Instance as a Linked Server. It’s using the Oracle Client OLEDB Provider. When doing an update in a transaction on a table having a CTXSYS.CONTEXT Text Index the DTC Connection aborts after approx. 10 Min with
Error: 8522, Severity: 18, State: 1 on the commit. As soon you remove the CONTEXT index it works again.
Is this a bug in OraOLEDB or can we do something to fix this issue, while keeping the indexes?
We use an ORACLE 11g 188.8.131.52.0
Create this table for this test.
CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200)); INSERT INTO docs VALUES(1, '<HTML>California is a state in the US.</HTML>'); INSERT INTO docs VALUES(2, '<HTML>Paris is a city in France.</HTML>'); INSERT INTO docs VALUES(3, '<HTML>France is in Europe.</HTML>'); INSERT INTO docs VALUES(4, '<HTML>Los Angeles is a city in California.</HTML>'); INSERT INTO docs VALUES(5, '<HTML>Mexico City is big.</HTML>'); COMMIT; CREATE INDEX idx_docs ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
We use a SQL Server 2012 Express and Developer Edition both produce the same error. Setup a linked server to above Oracle Server with Oracle Provider for OLE DB (OraOLEDB) Release 184.108.40.206.0 Production.
This updates work:
set xact_abort off; update DBLINK..REMOTE_USER.DOCS set text='<HTML>Mexico City is massive.</HTML>' where id = 5; (1 row(s) affected)
This Update Statement produces the error, after the DTC seems to run in a timeout.
set xact_abort on; begin transaction t1 update DBLINK..REMOTE_USER.DOCS set text='<HTML>Mexico City is huge.</HTML>' where id = 5; commit transaction t1; (1 row(s) affected) Msg 8522, Level 18, State 1, Line 4 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
This the end of the trace of a successful update:
This is the end of the trace of the update that runs into the timeout: