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