How To: Upgrade an Oracle geodatabase after applying the Spatial Reference Consistency Patch
The Spatial Reference Consistency Patch released for ArcGIS for 10.2.1 and 10.2.2 (Desktop, Engine and Server) corrects inconsistencies between a feature class’ spatial reference and the geodatabase metadata.
After applying the Spatial Reference Consistency Patch, the geodatabase must be upgraded by the administrator and in some situations feature classes require their spatial indexes to be dropped and re-created.
This article outlines the steps for upgrading the geodatabase and identifying whether any feature classes' spatial indexes must be re-created.
After applying the Spatial Reference Consistency Patch, the geodatabase must be upgraded. The geodatabase upgrade detects and corrects any inconsistent spatial reference metadata. In addition, triggers are added to various geodatabase data dictionary objects to prevent spatial reference inconsistencies from being introduced by ArcGIS applications without the Spatial Reference Consistency Patch.
Even after executing the geodatabase upgrade, there may be situations in which feature classes with inconsistent spatial reference values must have their spatial indexes re-created.
If the spatial indexes are not re-created, one can encounter the following errors:
"Underlying DBMS error [ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE."
"Underlying DBMS error [ORA-29869: cannot issue ALTER without REBUILD on a domain index marked FAILED."
- Determine which feature classes must have spatial indexes created.
The following query returns any feature class that do not have a spatial index:
SELECT owner, table_name
WHERE (owner, table_name, column_name) NOT IN
(SELECT owner, table_name, column_name
The next query returns feature classes that currently have an invalid spatial index:
SELECT owner, table_name
WHERE status <> 'VALID'
AND ityp_owner = 'SDE'
AND ityp_name = 'ST_SPATIAL_INDEX';
This query does not return invalid indexes for which the user does not have privileges. It is recommended that the query be executed by a user with elevated permissions.
- For each table returned, use ArcCatalog to drop and create the spatial index.
- ArcGIS 10.2.1 for (Desktop, Engine, Server) Spatial Reference Consistency Patch
- ArcGIS 10.2.2 for (Desktop, Engine, Server) Spatial Reference Consistency Patch
- KB42335: Invalid spatial reference (4326) set when inserting/updating the sde gdb_items table
- KB42461: Unable to insert or update Oracle st_geometry feature classes, encountering ORA-20085 errors