BUG
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:Once the import completes, querying the sde.st_geometry_columns table shows the owner of the imported table to be the user who performed the import, not the actual owner of the table specified in the remap_schema option.
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
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.
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;
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
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');
Code:
C:\> impdp sde/sde dumpfile=data_pump_dir:parcels.dmp schemas=assessor
Article ID:000010718
Get help from ArcGIS experts
Download the Esri Support App