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 MAY 07, 2021

Applies to:

Oracle Data Integrator - Version to [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) and later Work Repositories, are used to store:

Stored informationODI 10gODI 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
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)
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:

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
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
 Analyze the "orphan" records in ODI and later.
 Step 6. Delete the "orphan" records.

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