English

Bug: IMP-00017: following statement failed with ORACLE error 29855

Description

IMP-00017: following statement failed with ORACLE error 29855:
"CREATE INDEX "A197_IX1" ON "USACAPITALS" ("FEATURE" ) INDEXTYPE IS "MDSYS""
"."SPATIAL_INDEX" PARAMETERS ('SDO_COMMIT_INTERVAL = 1000')"
IMP-00003: ORACLE error 29855 encountered
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in spatial index: [mdrcrtxfergm]
ORA-13365: Layer SRID does not match geometry SRID
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 7
ORA-06512: at line 1

Cause

Importing Oracle 8.1.7 dump files into Oracle 9i may fail if they contain Oracle Spatial feature classes loaded using shp2sde prior to ArcSDE 8.2 Patch 1. This error occurs when trying to build the Oracle Spatial spatial index. As mentioned in the error message, the SRID in the layer does not match the SRID in the one or more geometries, or more precisely, the SRID in the entry in USER_SDO_GEOM_METADATA for the table does not match MDSYS.SDO_GEOMETRY.SDO_SRID in one or more rows in the table.

Prior to ArcSDE 8.2 Patch 1, ArcSDE did not check whether the Oracle Spatial SRID in USER_SDO_GEOM_METADATA for a table matched the SDO_GEOMETRY.SDO_SRID in each row of the table. In Oracle 8i, this was not required. It is now required in Oracle 9i, and ArcSDE was changed to meet this new requirement in ArcSDE 8.2 Patch 1. Now, importing a shapefile into an Oracle Spatial table using ArcCatalog sets the Oracle Spatial SRID to NULL in each feature's record and in USER_SDO_GEOM_METADATA.

Workaround

After loading Oracle Spatial data using an earlier version of shp2sde, each row in the new table can be updated to have an SRID that matches the one found in USER_SDO_GEOM_METADATA, and then rebuild the spatial index. 2-D R-Tree spatial indexes should be used with Oracle 9i.

Note:
Before updating the SRID in all geometries in the table to have the same SRID, be sure that this is a valid operation for the given geometries. Depending on the source of the data, it is possible that the geometries really do have different coordinate systems. If that is the case, then a better solution is to divide the geometries into multiple tables, each containing geometries with the same SRID. All geometries in a table must share the same SRID in order to build a spatial index.

  1. Update the SRID in each row of the table. If there is a valid SRID in USER_SDO_GEOM_METADATA, all of the rows can be updated like the following:

    Code:
    update OWNERNAME.MYTABLE a
    set a.SHPCOLUMN.sdo_srid =
    (select srid
    from all_sdo_geom_metadata
    where table_name = 'MYTABLE'
    and owner = 'OWNERNAME');
    commit;

  2. Create the spatial index. Because ArcSDE uses hints in some places, name the spatial index with names that ArcSDE can recognize. The names for the indexes are the same across all platforms. The spatial indexes are named 'Ann_IX1', where 'nn' is the LAYER_ID for the table in SDE.LAYERS. The spatial index on the ADDs table for the layer is called 'Ann_IX1_A'. To create an R-Tree spatial index, follow the example below:

    Code:
    create index A00_IX1
    on MYTABLE(SHPCOLUMN)
    indextype is mdsys.spatial_index
    parameters ('sdo_indx_dims=2');