Oracle eGDB and Spatial Grid Size - Confused?

Question

Hello Everyone

A user today gave me a fGDB with a Polygon Feature Class PHK_PY (FC) with around 1000 features to load into our Oracle eGDB. This FC had three Spatial Grid Levels set G1 380 G2 1200 G3 3500.

Current environment:

Geodatabase 10.7.
ArcGIS Pro 2.9.4
Oracle 18.6.0.0
ST_GEOMETRY
Windows

When I used ArcGIS Pro to copy and paste it into the eGDB I got the following error:

Exception: ERROR 000260: Underlying DBMS error [Error executing PL/SQL Block db_stgeom_create_index::ORA-29855:
Fehler bei Ausführung der Routine ODCIINDEXCREATE]--DBMS table not found [ORA-04043: Objekt GEOTEST.PHK_PY ist nicht vorhanden]
[GEOTEST.PHK_PY] ERROR 000260: Underlying DBMS error [Error executing PL/SQL Block db_stgeom_create_index::ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE]--
DBMS table not found [ORA-04043: Objekt GEOTEST.PHK_PY ist nicht vorhanden]
[GEOTEST.PHK_PY] Failed to execute (Copy).

I then used the Add Spatial Index Tool and set the Spatial Grid Levels to G1 380 G2 0 G3 0 in the fGDB - this then imported correctly into the eGDB.

So my question is: Are the Spatial Grid Levels G2 and G3 important for eGDB - whereas I read that these are ignored by ArcGIS Pro - I have been working with eGDB/Oracle/SDE since 2005 and I'm a bit confused?

I have a vague memory of this issue but that was way back in 2009 or so....

Feedback welcome and appreciated.

MMBR · Answer

@JamesMorrison1 - yes you can drop the Spatial Index using the Geoprocessing Tool "Remove Spatial Index" or you can use the Featureclass properties dialog to do the same.

JamesMorrison1 · Answer

Hello Marcelo

Thank you very much for the detailed reply now I'm no longer confused.

* Option 1 - We are currently migrating to 19c and Geodatabase Repository to 10.9.1.2.9 ready Janury 2023.
* Option 2 - Worked fine and a good short-term workaround. Thanks.
* Option 3 - You wrote, "drop the Oracle Featureclass Spatial Index first" I assume using for example arcpy.management.RemoveSpatialIndex() before the append?

Once again thank you for the reply your answers are always very helpful.

MMBR · Answer

See below.

a. Geodatabase ArcSDE Repository Version 10.7 is too old if you are using ArcGIS Pro 2.9.4

you must upgrade you Geodatabase Repository to 10.9.1.2.9 using ArcGIS Pro 2.9.4

Client and geodatabase compatibility—ArcGIS Pro | Documentation

Note: 
Oracle Enterprise Geodatabase ArcSDE Repository Upgrade Using Oracle Restore Point

b. Spatial Index options for Grid 1, Grid 2, Grid 3 are now deprecated in ArcGIS Pro

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-spatial-index.htm

in ArcMap / ArcCatalog - 10.8.1 these options are still valid though.
Add Spatial Index (Data Management)—ArcMap | Documentation (arcgis.com)

c. These were probably the reasons you received the error "db_stgeom_create_index::ORA-29855" when you tried to use ArcGIS Pro to copy and paste the featureclass from the FileGeodatabase into the Oracle Geodatabase.

Workarounds:

Option 1: upgrade the Oracle Enterprise Geodatabase Repository to 10.9.1.2.9 using ArcGIS Pro 2.9.4 then try the copy and paste again

Option 2: delete the spatial index in the FileGeodatabae Featureclass then try to copy and paste

Option 3: export FileGeodatabae Featureclass to XML document schema-only, then import XML into Oracle Geodatabase to recreate the Featureclass empty, then use Pro Append Data Tool to load the data from the FileGeodatabase Featureclass into the Oracle Featureclass, note to make the data load faster drop the Oracle Featureclass Spatial Index first, after the load is done the spatial index is recreated automatically.

Note:   How Load Large Featureclass Oracle Geodatabase

Oracle Version Note:

Oracle 18c was deprecated by Oracle, 18c reached end of life and Oracle announced there will be no more Oracle Database 18c Patches. Oracle Database Customers need to move to 19c the new long term Oracle Release.