topic Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine in Data Management Questions

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

EmadAl-Mousa — Tue, 06 May 2014 03:17:44 GMT

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.

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

MarcoBoeringa — Tue, 06 May 2014 08:32:08 GMT

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.

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

EmadAl-Mousa — Tue, 06 May 2014 10:00:10 GMT

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;

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

MarcoBoeringa — Tue, 06 May 2014 10:13:52 GMT

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.

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

EmadAl-Mousa — Tue, 06 May 2014 11:13:52 GMT

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,

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

MarcoBoeringa — Tue, 06 May 2014 12:19:44 GMT

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.

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

MarcoBoeringa — Tue, 06 May 2014 12:34:41 GMT

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

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

VinceAngelo — Tue, 06 May 2014 13:33:28 GMT

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

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

EmadAl-Mousa — Tue, 10 Jun 2014 06:51:10 GMT

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,