Qlik Replicate and Oracle target endpoint: ORA-14452 attempt to create, alter or drop an index on temporary table already in use

[john_wang]

The Oracle target endpoint occasionally encounters an error during the CDC stage. These issues arise specifically after abnormal disconnections between the Replicate server and the Oracle server. The error, ORA-14452, occurs when attempting to perform DDL operations while the table is either not truncated or has active sessions.

The error message in task log file:

[TARGET_APPLY ]I: Failed executing drop table statement: DROP TABLE "SCOTT"."attrep_changes7DA25A24_0000001" [1020403]
[TARGET_APPLY ]I: ORA-14452: attempt to create, alter or drop an index on temporary table already in use [1022307]

To resolve this issue, we need to kill the open sessions by below steps.

Resolution:

1. Query the object_ID of the net change table:  

   select object_id from dba_objects where object_name='attrep_changes7DA25A24_0000001';
   -------
   1192406

2. Get the SessionID based on the object_id:  

   select sid from v$lock where id1=1192406;
   --------
   608

3. Query the SERIAL# based on the SessionID:

   select serial# from v$session where sid=608;
   ----
   10893

4. Kill the open session:

   ALTER SYSTEM KILL SESSION '608,10893';

Internal Investigation ID(s):

#00167394

Environment:

• Qlik Replicate All versions
• Oracle All versions

Qlik Replicate