My Current Environment: Oracle Database 11gR2 (126.96.36.199) 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).
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.
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.
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.
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.