Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine

[EmadAl-Mousa]

Hi,

My Current Environment:
Oracle Database 11gR2 (11.2.0.3)
ArcSDE 10.2
Server: IBM AIX 6.1

I have a geodatabase schema where it used to be stored in ???SDEBINARY???, I have converted it to ???st_geometry??? successfully with no problems.

I can view the data using the ???oracle schema owner user??? with no issues in ArcCatalog. However, when I try to view the data using another Oracle user (application user) using ArcCatalog who has full read/edit permissions on these layers I face the following error:

Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine
ORA-01031: insufficient privileges
ORA-06512: at "SDE.SPX_UTIL",line 847
ORA-06512: at "SDE.SPX_UTIL",line 891
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 105

please refer also to the attached snapshot image of the error ( i have wiped the layer name for security reason).


did somebody face this error before ?

Thanks in advance.

[MarcoBoeringa]

How did you create this second user? Have you already attempted to use the build-in tools of ArcGIS for creating the database user, and granting privileges? You may be overlooking something if you do this at the database level.

[EmadAl-Mousa]

the non-data owner account is created through oracle normally with full permission(select,insert,update,delete) i always created these accounts are working fine with "sdebinary" data.

the account sql definition would look like this:

  CREATE USER xyz_app
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE TS_USER_DATA_01
  TEMPORARY TABLESPACE TS_TEMP_DATA_01
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

grant rl_edit_access to xyz_app;
grant create session to xyz_app;
alter user xyz_app default role all;



 



 

[MarcoBoeringa]

I really would make an attempt to create a new user using the build-in tools of ArcGIS for Desktop (right click the Database Connection in ArcCatalog and choose Administration / Add User or use the Create Database User tool). ST_Geometry storage is just to different from SDE_Binary to assume you can use Oracle scripts suitable for SDE_Binary in an ST_Geometry environment.

Also see the Create Database User tool Help for what database privileges are necessary and granted by the tool.

[EmadAl-Mousa]

Hi Marco,

Thanks for your replay, i even tried to grant the user "DBA" privilege temporarely to check maybe a missing privilege is required. and shockingly i am still receving the same error.

also, i found out that i can see the table records in ArcCatalog under the "Preview" tab when i choose "Table".....However, when i choose "geogrpahy" option the error pops up.

i have also tried rebuilding and analyzing the index through geoprocessing tools, and still facing the same problem.

Regards,

[MarcoBoeringa]

also, i found out that i can see the table records in ArcCatalog under the "Preview" tab when i choose "Table".....However, when i choose "geography" option the error pops up.

Are you sure you have configured the ST_Geometry type correctly on this database? The fact that you are able to see the table records, but not the geometries, pretty much excludes a rights / privileges issue with this specific table and / or user. This more suggests some misconfiguration of the ST_Geometry type.

See Add the ST_Geometry type to an Oracle database in the Help.

*** EDIT ***: overlooked / forgot it again, but you already wrote that you managed to view the spatial data with schema owner, so it is probably configured right.

[MarcoBoeringa]

It is probably unrelated, but I noticed there is an old ESRI bug reference for ArcGIS 9.3.1 with this same Oracle error (ORA-29902):

Bug:  Encountering Oracle error ORA-29902: error in executing ODCIIndexStart() routine when creating spatial index with st_geometry

[VinceAngelo]

This is most certainly an issue to take to Tech Support.  I will note that the permissions on
the table of a non-owner, even DBA rights, are of no use when building a spatial index on
a table that user does not own.  A draw request should not attempt to build an index,
especially one that is already built.  The character set used in the database, and the exact
user names involved will be necessary for the TS analyst to assist you.

- V

[EmadAl-Mousa · Accepted solution]

Thanks for ESRI Tech Support, after extensive troubleshooting.....we found out that there are permissions required to be granted to "PUBLIC".

in our environment for "security" reason we refrain from granting permissions to "PUBLIC".

now the non-data owner (application user) can view the layers stored in st_geomertry successfully.


Regards,