Problem: Oracle Data Pump Import with the Append or Truncate Option Corrupts ST

Description

Using Oracle Data Pump Import with the table_exists_action set to append or truncate drops the st_spatial_index domain index table, corrupting the ST_Geometry attribute table.

The following example demonstrates using the Oracle Data Pump Import utility with a table named BUILDING and the append option. The data from the export file is appended to the building table, but the st_spatial_index "S154_IDX$" is dropped.

Code:
D:\>impdp tb/tb TABLES=building dumpfile=expdir:build.dmp table_exists_action=append

Import: Release 10.2.0.3 - Production on Monday, 07 January, 2008 20:34:39
Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "TB"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SDE"."SYS_IMPORT_TABLE_01":  tb/******** TABLES=building dumpfile=expdir:build.dmp table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TB"."BUILDING" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SDE"."BUILDING"                               25.90 KB      13 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE_DATA
. . imported "TB"."S154_IDX$"                           8.773 KB      21 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Job "TB"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:35:11

Cause

The internal cause on why the domain index table is being dropped is unknown. A technical incident with Oracle has been opened and when a TAR file is created that identifies the issue, the number is published.

Solution or Workaround

The solution, to avoid the data corruption, is not to use the table_exists_action option when using Oracle Data Pump Import.

If the objective is to import data with the table_exists_action set to append or truncate, first perform the export of the data without the spatial index. In the database where the export is going to be performed, first drop the index and after, perform the export. This allows the import to proceed with appending or truncating the object being imported. When appending, the inserted rows geometries are automatically be indexed.

If one encounters the case where the domain index table is dropped by the Oracle Data Pump Import utility, one must manually drop the index on the ST_Geometry attribute and create the spatial index in ArcCatalog or using SQL.

Article ID:000009819

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Oracle Data Pump Import with the append or truncate option corrupts ST_Geometry tables

Description

Cause

Solution or Workaround

Discover more on this topic