Is This Content Helpful?
We're glad to know this article was helpful.
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.
C:\> impdp sde/sde dumpfile=data_pump_dir:parcels.dmp remap_schema=assessor:tb
SQL> SELECT owner, table_name FROM sde.st_geometry_columns;
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.
There are two options available to avoid the invalid metadata entries from being created during the import. Either drop the spatial index on the table with the st_geometry attribute prior to performing the export, or do not use the Oracle remap_schema option when performing the data pump import.
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.
SQL> set long 99999
SQL> SELECT dbms_metadata.get_ddl('INDEX', 'A1_IX1','ASSESSOR') FROM dual;
SQL> SELECT index_name FROM sde.st_geometry_index WHERE table_name = 'PARCELS' and owner = 'ASSESSOR';
SQL> DROP INDEX A1_IX1;
C:\> expdp sde/sde dumpfile=data_pump_dir:parcels.dmp schemas=assessor
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');
C:\> impdp sde/sde dumpfile=data_pump_dir:parcels.dmp schemas=assessor