How To Determine Orphan Records from the ODI SNP_EXP_TXT/SNP_EXP_TXT_HEADER Work Repository Table
(Doc ID 749487.1)
Last updated on MAY 07, 2021
Oracle Data Integrator - Version 10.1.3.2.0 to 22.214.171.124.99 [Release 10gR3 to 11gR1] Information in this document applies to any platform.
The SNP_EXP_TXT and SNP_EXP_TXT_HEADER tables which are located in the Oracle Data Integrator (ODI) 10.1.3.2.0 and later Work Repositories, are used to store:
ODI 126.96.36.199 and later
Error messages which indicate that the Session (execution) was unable to start.
Error messages related to the Graphical User Interface or one object, aside executions (example: com.sunopsis.core.i: Invalid object, it has been updated by another user)
Execution related records, such as:
Session Parameters and Keywords
Session, Step and Task warnings and error messages
Default and current values of Session Variables
Scheduled Scenarios Variable values
Scenario definition information:
Default and current values of Scenario Variables
Scenario Folder, Scenario and Variables Description Fields (comments)
Information related to Text Type Variables:
Historized values of "Text" type Variables
Load Plan related information, such as:
Load Plan, Load Plan Instance, Load Plan Step, and Load Plan Execution Descriptions (comments)
Load Plan, Load Plan Instance, Load Plan Step, and Load Plan Execution Variables
Load Plan, Load Plan Instance, Load Plan Step, and Load Plan Execution Error messages
For multiple reasons, the SNP_EXP_TXT/SNP_EXP_TXT_HEADER table may hold a number of orphan records. These hanging foreign keys are references to Sessions that have been deleted:
Problems fixed in version 10.1.3.4.2 of ODI (including incorrect behavior of OdiPurgeLog tool)
Partial delete operations when using the trash icon to purge Sessions from the ODI Operator for which an execution Log Level of less than 5 was used (see unpublished Bug 7393196)
Missing SQL instructions used to manually purge executions (note that the SNP_EXP_TXT table did not exist prior to version 10.1.3.2.0 of ODI; and the SNP_EXP_TXT_HEADER was introduced in ODI 188.8.131.52.0).
Variable values that are not deleted when using the OdiPurgeLog tool or the trash icon to purge Sessions from the ODI Operator (see unpublished Bug:8640312).
Orphans are those records that are no longer referenced by any ODI component or process. Accumulating such unuseful records requires storage space, and leads to potential issues such as high resource consumption, increased latency, and other performance issues.
It is therefore strongly recommended to identify, and delete such records from the SNP_EXP_TXT / SNP_EXP_TXT_HEADER table.
The goal of this document is to explain how to identify "orphan" records and how to perform the appropriate deletion operation on the SNP_EXP_TXT and SNP_EXP_TXT_HEADER tables.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!