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

Applies to:

Oracle Data Integrator - Version 10.1.3.2.0 to 11.1.1.9.99 [Release 10gR3 to 11gR1]
Information in this document applies to any platform.

Purpose

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:

Stored information ODI 10g ODI 11.1.1.3 ODI 11.1.1.5 and later

Error messages which indicate that the Session (execution) was unable to start. SNP_EXP_TXT SNP_EXP_TXT SNP_EXP_TXT_HEADER

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) SNP_EXP_TXT SNP_EXP_TXT SNP_EXP_TXT_HEADER

Execution related records, such as:

Session Parameters and Keywords

Session, Step and Task warnings and error messages

Scenario Reports

Default and current values of Session Variables

SNP_EXP_TXT SNP_EXP_TXT SNP_EXP_TXT_HEADER

Scheduled Scenarios Variable values - - SNP_EXP_TXT_HEADER

Scenario definition information:

Default and current values of Scenario Variables

Scenario Folder, Scenario and Variables Description Fields (comments)

SNP_EXP_TXT SNP_EXP_TXT SNP_EXP_TXT_HEADER

Information related to Text Type Variables:

Historized values of "Text" type Variables

SNP_EXP_TXT SNP_EXP_TXT SNP_EXP_TXT_HEADER

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

- - SNP_EXP_TXT_HEADER

Stored information	ODI 10g	ODI 11.1.1.3	ODI 11.1.1.5 and later
Error messages which indicate that the Session (execution) was unable to start.	SNP_EXP_TXT	SNP_EXP_TXT	SNP_EXP_TXT_HEADER
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)	SNP_EXP_TXT	SNP_EXP_TXT	SNP_EXP_TXT_HEADER
Execution related records, such as: Session Parameters and Keywords Session, Step and Task warnings and error messages Scenario Reports Default and current values of Session Variables	SNP_EXP_TXT	SNP_EXP_TXT	SNP_EXP_TXT_HEADER
Scheduled Scenarios Variable values	-	-	SNP_EXP_TXT_HEADER
Scenario definition information: Default and current values of Scenario Variables Scenario Folder, Scenario and Variables Description Fields (comments)	SNP_EXP_TXT	SNP_EXP_TXT	SNP_EXP_TXT_HEADER
Information related to Text Type Variables: Historized values of "Text" type Variables	SNP_EXP_TXT	SNP_EXP_TXT	SNP_EXP_TXT_HEADER
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	-	-	SNP_EXP_TXT_HEADER

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