My Oracle Support Banner

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 FEBRUARY 03, 2019

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 informationODI 10gODI 11.1.1.3ODI 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:

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.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps
 Step 1. Stop all Oracle Data Integrator processes.
 Step 2. Identify the "orphan" records.
 Step 3. Backup the database.
 Step 4. Connect to the Work Repository.
 Step 5. Analyze the "orphan" records.
 Analyze the "orphan" records in ODI 10g and ODI 11.1.1.3.
 Analyze the "orphan" records in ODI 11.1.1.5 and later.
 Step 6. Delete the "orphan" records.
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.