Bug: Invalid spatial reference (4326) set when inserting/updating the sde gdb_items table when modifying the content for upgraded ArcGIS 10.0 geodatabases
NIM099594 - Oracle errors, ORA-29877, ORA-20085 and ORA-06512 are encountered when attempting to make object schema changes, spatial index changes, add Global IDs, register data as versioned (Register as Versioned tool), or create a raster dataset in ArcCatalog after upgrading certain ArcSDE 10.0 Oracle geodatabases.
The following error message is returned when performing an operation that encounters the bug:
"ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-20085: Insert Spatial Reference SRID 4326 does not match
SDE.GDB_ITEMS.SHAPE registered Spatial Reference SRID 0
ORA-06512: in "SDE.ST_DOMAIN_METHODS", line 2000
ORA-06512: in "SDE.ST_DOMAIN_METHODS", line 2372"
This is a critical issue where users are unable to create new feature classes or modify their existing schema after upgrading to ArcGIS 10.1 or higher. This issue only occurs with Oracle geodatabases that were upgraded from a previous release to ArcGIS 10.0, or Oracle geodatabases created with the ArcGIS 10.0 release.
This includes Oracle geodatabases that were:
• upgraded to 10.0 and then upgraded to 10.1, 10.2.1 or 10.2.2, or
• created at 10.0 and upgraded to 10.1, 10.2.1 or 10.2.2
These errors are not encountered with Oracle geodatabases upgraded directly from 9.3.1 to 10.1 or higher. Oracle geodatabases created at 10.1 and later releases are not impacted.
Esri is currently working on a general patch for NIM099594. Please refer back to this article for updated information.
The Spatial Reference Consistency patches for ArcGIS 10.1 SP1 QIP, 10.2.1 and ArcGIS 10.2.2 (Desktop, Engine, Server) have been released:
• Spatial Reference Consistency patch for ArcGIS 10.1 SP1 QIP
• Spatial Reference Consistency patch for ArcGIS 10.2.1
• Spatial Reference Consistency patch for ArcGIS 10.2.2
For geodatabases created with ArcGIS 10.0, the sde.gdb_items table’s spatial attribute and spatial index use the GCS_WGS_1984 spatial reference and an internal SRID property of 0.
For geodatabases created since ArcGIS 10.1 and later, the sde.gdb_items table's spatial reference continue to use the GCS_WGS_1984 spatial reference, but use an internal SRID property of 4326 (the common EPSG value).
When attempting to modify an object in the geodatabase which inserts a new feature in the sde.gdb_items table or updates an existing feature, ArcGIS mistakenly sets the feature's SRID value to 4326 and not the required SRID value 0. The bug exists in released versions of ArcGIS 10.2.2, 10.2.1, and the 10.1 SP1 QIP.
One of the intentions of the gdb_items table in the geodatabase catalog is to allow applications to persist geometries representing the cataloged item and the ability to discover items in the catalog via spatial searches.
The recommended workaround for NIM099594 is to drop the spatial index on the sde.gdb_items table.
1. Connect to the Oracle instance/geodatabase using SQL*Plus as the sde user.
2. Identify the name of the gdb_items spatial index:
SQL> SELECT index_name FROM user_ind_columns WHERE table_name = 'GDB_ITEMS' and column_name = 'SHAPE';
3. Drop the spatial index:
SQL> DROP INDEX A29_IX1;
The spatial index ensures that all features have the same SRID value. If an invalid SRID value is set for a feature during an insert or update, the statement encounters the errors ORA-29877, ORA-20085, ORA-06512. When no spatial index is present, an st_geometry attribute is able to persist features with mixed spatial reference values.
Presently, no releases of ArcGIS products execute spatial queries against the sde.gdb_items table. Temporarily dropping the spatial index does not impact functionality, performance or scalability of ArcGIS. The Spatial Reference Consistency Patch executes the geodatabase upgrade command that will create the sde.gdb_items spatial index.
If you are an Oracle-based customer that has upgraded an ArcGIS 10.0 geodatabase to ArcGIS 10.1 or higher and problems have been encountered not cited in this knowledge base article, please contact Esri Technical Support for further review.
- Bug: NIM099594
- KB42365: An issue exists that may impact Oracle geodatabases after upgrading to ArcGIS Runtime SDK�
- KB42775: Error: Invalid SDE transaction or Spatial Reference for output is invalid
- KB42776: HowTo: Upgrade an Oracle geodatabase after applying the Spatial Reference Consistency Patch