Using Oracle data pump with the option table_exists_action=append drops the spatial index

Description

A table's st_spatial_index object is dropped without a warning or error when using Oracle's Data Pump Import utility with the option table_exists_action=append or truncate.

The following example demonstrates using the Data Pump Import utility with a table named sewers in the tomb schema. The option table_exists_action=append informs the import utility to append the data in the export file to the existing table.

Even though the output message 'imported "TOMB"."S16_IDX$"' indicates the st_spatial_index object is imported, Oracle, without warning or error, drops the table.

D:\Oracle\admin\geodb\dumpdir>impdp tomb/tomb tables=sewers directory=expdir dum
pfile=expdir:sewers.dmp table_exists_action=append

Import: Release 10.2.0.3.0 - Production on Sunday, 24 February, 2008 17:17:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TOMB"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TOMB"."SYS_IMPORT_TABLE_01":  tomb/******** tables=sewers directory=expdir dumpfile=expdir:sewers.dmp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TOMB"."SEWERS" 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 "TOMB"."SEWERS"                             19.53 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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 "TOMB"."S16_IDX$"                           8.070 KB       8 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Job "TOMB"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:18:06

After performing the import, the data is correctly appended to the existing table, but the st_spatial_index object has been dropped.

Cause

An internal Oracle condition causes the st_spatial_index on the table being imported to be mistakenly dropped, when the import is performed with the table_exists_action set to append or truncate.

Workaround

It is not recommended to use the table_exists_action append or truncate option, when importing data to an existing table.

If the data must be appended to an existing table using import, it is first recommended to drop the spatial index, perform the import with the append option, and then create the spatial index.

Creating the spatial index, after appending the data to the table, ensures that all rows are correctly indexed.