Using the Oracle data pump import option remap_schema creates inconsistent metadata

Description

When using Oracle's Data Pump utility to import an export file containing tables with st_geometry attributes and spatial indexes with the remap_schema option, the process creates invalid entries in the sde.st_geometry_columns and sde.st_geometry_index tables.

The invalid metadata in the sde.st_geometry_columns will have the owner of the table being imported as the user executing the datapump import operation, instead of the remap_schema user that owns the table.

The following example demonstrates using the Oracle Data Pump import utility with the remap_schema option. The sde user is executing the import operation with the remap_schema option. The remap_schema option specifies that the tables contained in the parcels.dmp file that are owned by a user named assessor should be imported into the tb user schema.

Code:
C:\> impdp sde/sde dumpfile=data_pump_dir:parcels.dmp remap_schema=assessor:tb

Code:
SQL> SELECT owner, table_name FROM sde.st_geometry_columns;

OWNER                          TABLE_NAME
------------------------------ ------------------
SDE                            PARCELS

Cause

The cause of the problem is occurring within the import process when the Oracle import utility calls the st_geometry domain index import metadata implementation. Within the st_type_export package, the insert statement used to write the metadata to the sde.st_geometry_columns table is qualifying the owner attribute by using the USER function, which represents the user session performing the import, not the remap_schema user.

Workaround

• The first option requires dropping the spatial index on the table prior to performing the Oracle data pump export. After the export, remember to re-create the spatial index in the origin Oracle instance.
  
  

  Note:
  Use the DBMS_METADATA package to obtain the DDL that can be used to re-create the spatial index. This step should be performed prior to dropping the index.
  
  

  Code:
  SQL> set long 99999
  SQL> SELECT dbms_metadata.get_ddl('INDEX', 'A1_IX1','ASSESSOR') FROM dual;
  

  
  
  
  Next, detect the spatial index name on the table and then drop the index prior to performing the data pump export.
  
  

  Code:
  SQL> SELECT index_name FROM sde.st_geometry_index WHERE table_name = 'PARCELS' and owner = 'ASSESSOR';
  
  INDEX_NAME
  ------------------------------
  A1_IX1
  
  SQL> DROP INDEX A1_IX1;
  
  C:\> expdp sde/sde dumpfile=data_pump_dir:parcels.dmp schemas=assessor

  
  
  Once the export is completed, create the spatial index on the table using the DDL from the dbms_metadata.get_ddl procedure.
  
  

  Code:
  SQL> CREATE INDEX a1_ix1 ON parcels (shape) INDEXTYPE IS sde.st_spatial_index PARAMETERS ('ST_GRIDS=64608.875068171
  6 ST_SRID=2 ST_COMMIT_ROWS=10000  PCTFREE 0 INITRANS 4');

  
  
• The second option is to not use the remap_schema option. Instead, specify the schemas option along with the original data owner name. This requires that the owner of the object being exported already exists in the target Oracle instance. For example, if the exported table and spatial index were owned by the assessor user, then the assessor schema must exist in the target Oracle instance prior to the file being imported.
  
  

  Code:
  C:\> impdp sde/sde dumpfile=data_pump_dir:parcels.dmp schemas=assessor

  
  
  If this problem has been encountered, it is safe to delete the invalid entry from the sde.st_geometry_columns table as the Oracle sde user. Then drop and create the spatial index on the imported table as the data owner. Creating a spatial index will write the correct metadata to the sde.st_geometry_columns and sde.st_geometry_index tables.