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

after converting my Geo-Database from Long Raw (SDEBINARY) to st_geometry, i found out that i can only see the feautre classes (layers) through schema accounts ONLY in ArcCatalog !!!

when i try to view the data with “application-user” accounts, i face the below 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

Image

**** Solution:

 

since we follow strict security implimentation on the Oracle Databases, we have revoked many ‘PUBLIC’ role permissions in ArcSDE Geodatabase Systems, and it seems these permissions are required.

so you need to make sure that the following permissions listed are “granted“:

grant UPDATE on sde.ALL_ST_GEOMETRY_COLUMNS_V to  app_user;                       
grant SELECT on sde.ALL_ST_GEOMETRY_COLUMNS_V to  app_user;                       
grant INSERT on sde.ALL_ST_GEOMETRY_COLUMNS_V to  app_user;                       
grant DELETE on sde.ALL_ST_GEOMETRY_COLUMNS_V to  app_user;                       
grant EXECUTE on sde.ARCHIVE_UTIL to  app_user;                                   
grant EXECUTE on sde.BLOB_ARRAY_TAB to  app_user;                                 
grant EXECUTE on sde.BND_ROWID_TAB to  app_user;                                  
grant SELECT on sde.COLUMN_REGISTRY to  app_user;                                 
grant SELECT on sde.CONNECTION_ID_GENERATOR to  app_user;                         
grant SELECT on sde.DBTUNE to  app_user;                                          
grant EXECUTE on sde.DBTUNE_UTIL to  app_user;                                    
grant EXECUTE on sde.FLT_ARRAY_TAB to  app_user;                                  
grant INSERT on sde.GDB_ITEMRELATIONSHIPS to  app_user;                           
grant SELECT on sde.GDB_ITEMRELATIONSHIPS to  app_user;                           
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS to  app_user;                           
grant DELETE on sde.GDB_ITEMRELATIONSHIPS to  app_user;                           
grant INSERT on sde.GDB_ITEMRELATIONSHIPS_VW to  app_user;                        
grant SELECT on sde.GDB_ITEMRELATIONSHIPS_VW to  app_user;                        
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS_VW to  app_user;                        
grant DELETE on sde.GDB_ITEMRELATIONSHIPS_VW to  app_user;                        
grant UPDATE on sde.GDB_ITEMRELATIONSHIPTYPES to  app_user;                       
grant SELECT on sde.GDB_ITEMRELATIONSHIPTYPES to  app_user;                       
grant INSERT on sde.GDB_ITEMRELATIONSHIPTYPES to  app_user;                       
grant DELETE on sde.GDB_ITEMRELATIONSHIPTYPES to  app_user;                       
grant UPDATE on sde.GDB_ITEMS to  app_user;                                       
grant SELECT on sde.GDB_ITEMS to  app_user;                                       
grant INSERT on sde.GDB_ITEMS to  app_user;                                       
grant DELETE on sde.GDB_ITEMS to  app_user;                                       
grant UPDATE on sde.GDB_ITEMS_VW to  app_user;                                    
grant DELETE on sde.GDB_ITEMS_VW to  app_user;                                    
grant INSERT on sde.GDB_ITEMS_VW to  app_user;                                    
grant SELECT on sde.GDB_ITEMS_VW to  app_user;                                    
grant SELECT on sde.GDB_ITEMTYPES to  app_user;                                   
grant UPDATE on sde.GDB_ITEMTYPES to  app_user;                                   
grant INSERT on sde.GDB_ITEMTYPES to  app_user;                                   
grant DELETE on sde.GDB_ITEMTYPES to  app_user;                                   
grant SELECT on sde.GDB_REPLICALOG to  app_user;                                  
grant UPDATE on sde.GDB_REPLICALOG to  app_user;                                  
grant INSERT on sde.GDB_REPLICALOG to  app_user;                                  
grant DELETE on sde.GDB_REPLICALOG to  app_user;                                  
grant UPDATE on sde.GDB_TABLES_LAST_MODIFIED to  app_user;                        
grant SELECT on sde.GDB_TABLES_LAST_MODIFIED to  app_user;                        
grant DELETE on sde.GDB_TABLES_LAST_MODIFIED to  app_user;                        
grant INSERT on sde.GDB_TABLES_LAST_MODIFIED to  app_user;                        
grant EXECUTE on sde.GDB_UTIL to  app_user;                                       
grant SELECT on sde.GEOMETRY_COLUMNS to  app_user;                                
grant UPDATE on sde.INSTANCES to  app_user;                                       
grant SELECT on sde.INSTANCES to  app_user;                                       
grant INSERT on sde.INSTANCES to  app_user;                                       
grant DELETE on sde.INSTANCES to  app_user;                                       
grant EXECUTE on sde.INSTANCES_UTIL to  app_user;                                 
grant SELECT on sde.INSTANCE_ID_GENERATOR to  app_user;                           
grant EXECUTE on sde.INT_ARRAY_TAB to  app_user;                                  
grant EXECUTE on sde.KEYSET_UTIL to  app_user;                                    
grant SELECT on sde.LAYERS to  app_user;                                          
grant EXECUTE on sde.LAYERS_UTIL to  app_user;                                    
grant SELECT on sde.LAYER_ID_GENERATOR to  app_user;                              
grant SELECT on sde.LAYER_LOCKS to  app_user;                                     
grant EXECUTE on sde.LAYER_STATS_UTIL to  app_user;                               
grant SELECT on sde.LINEAGES_MODIFIED to  app_user;                               
grant SELECT on sde.LOCATORS to  app_user;                                        
grant SELECT on sde.LOCATOR_ID_GENERATOR to  app_user;                            
grant EXECUTE on sde.LOCATOR_UTIL to  app_user;                                   
grant EXECUTE on sde.LOCK_UTIL to  app_user;                                      
grant EXECUTE on sde.LOGFILE_UTIL to  app_user;                                   
grant SELECT on sde.METADATA to  app_user;                                        
grant SELECT on sde.METADATA_ID_GENERATOR to  app_user;                           
grant EXECUTE on sde.METADATA_UTIL to  app_user;                                  
grant SELECT on sde.MVTABLES_MODIFIED to  app_user;                               
grant SELECT on sde.OBJECT_LOCKS to  app_user;                                    
grant EXECUTE on sde.PINFO_UTIL to  app_user;                                     
grant SELECT on sde.PROCESS_INFORMATION to  app_user;                             
grant SELECT on sde.R2 to  app_user;                                              
grant SELECT on sde.R3 to  app_user;                                              
grant SELECT on sde.R4 to  app_user;                                              
grant SELECT on sde.R5 to  app_user;                                              
grant SELECT on sde.R6 to  app_user;                                              
grant EXECUTE on sde.RASTERCOLUMNS_UTIL to  app_user;                             
grant SELECT on sde.RASTERCOLUMN_ID_GENERATOR to  app_user;                       
grant SELECT on sde.RASTER_COLUMNS to  app_user;                                  
grant EXECUTE on sde.REGISTRY_UTIL to  app_user;                                  
grant EXECUTE on sde.SDENUMTAB to  app_user;                                      
grant EXECUTE on sde.SDEXMLTOTEXT to  app_user;                                   
grant SELECT on sde.SDE_ARCHIVES to  app_user;                                    
grant SELECT on sde.SDE_LAYER_STATS to  app_user;                                 
grant SELECT on sde.SDE_LOGFILE_POOL to  app_user;                                
grant UPDATE on sde.SDE_TABLES_MODIFIED to  app_user;                             
grant SELECT on sde.SDE_TABLES_MODIFIED to  app_user;                             
grant EXECUTE on sde.SDE_UTIL to  app_user;                                       
grant SELECT on sde.SDE_XML_COLUMNS to  app_user;                                 
grant UPDATE on sde.SDE_XML_DOC1 to  app_user;                                    
grant SELECT on sde.SDE_XML_DOC1 to  app_user;                                    
grant DELETE on sde.SDE_XML_DOC1 to  app_user;                                    
grant INSERT on sde.SDE_XML_DOC1 to  app_user;                                    
grant UPDATE on sde.SDE_XML_DOC2 to  app_user;                                    
grant SELECT on sde.SDE_XML_DOC2 to  app_user;                                    
grant INSERT on sde.SDE_XML_DOC2 to  app_user;                                    
grant DELETE on sde.SDE_XML_DOC2 to  app_user;                                    
grant UPDATE on sde.SDE_XML_DOC3 to  app_user;                                    
grant INSERT on sde.SDE_XML_DOC3 to  app_user;                                    
grant SELECT on sde.SDE_XML_DOC3 to  app_user;                                    
grant DELETE on sde.SDE_XML_DOC3 to  app_user;                                    
grant SELECT on sde.SDE_XML_DOC4 to  app_user;                                    
grant UPDATE on sde.SDE_XML_DOC4 to  app_user;                                    
grant INSERT on sde.SDE_XML_DOC4 to  app_user;                                    
grant DELETE on sde.SDE_XML_DOC4 to  app_user;                                    
grant SELECT on sde.SDE_XML_INDEXES to  app_user;                                 
grant SELECT on sde.SDE_XML_INDEX_TAGS to  app_user;                              
grant EXECUTE on sde.SDO_UTIL to  app_user;                                       
grant SELECT on sde.SERVER_CONFIG to  app_user;                                   
grant SELECT on sde.SPATIAL_REFERENCES to  app_user;                              
grant EXECUTE on sde.SPX_UTIL to  app_user;                                       
grant EXECUTE on sde.SP_GRID_INFO to  app_user;                                   
grant EXECUTE on sde.SREF_UTIL to  app_user;                                      
grant SELECT on sde.STATES to  app_user;                                          
grant SELECT on sde.STATE_LINEAGES to  app_user;                                  
grant SELECT on sde.STATE_LOCKS to  app_user;                                     
grant EXECUTE on sde.ST_AGGR_CONVEXHULL to  app_user;                             
grant EXECUTE on sde.ST_AGGR_INTERSECTION to  app_user;                           
grant EXECUTE on sde.ST_AGGR_UNION to  app_user;                                  
grant EXECUTE on sde.ST_AREA to  app_user;                                        
grant EXECUTE on sde.ST_ASBINARY to  app_user;                                    
grant EXECUTE on sde.ST_ASTEXT to  app_user;                                      
grant EXECUTE on sde.ST_BOUNDARY to  app_user;                                    
grant EXECUTE on sde.ST_BUFFER to  app_user;                                      
grant EXECUTE on sde.ST_BUFFER_INTERSECTS to  app_user;                           
grant EXECUTE on sde.ST_CENTROID to  app_user;                                    
grant EXECUTE on sde.ST_CONTAINS to  app_user;                                    
grant EXECUTE on sde.ST_CONVEXHULL to  app_user;                                  
grant EXECUTE on sde.ST_COORDDIM to  app_user;                                    
grant SELECT on sde.ST_COORDINATE_SYSTEMS to  app_user;                           
grant EXECUTE on sde.ST_CREF_UTIL to  app_user;                                   
grant EXECUTE on sde.ST_CROSSES to  app_user;                                     
grant EXECUTE on sde.ST_CURVE to  app_user;                                       
grant EXECUTE on sde.ST_DIFFERENCE to  app_user;                                  
grant EXECUTE on sde.ST_DIMENSION to  app_user;                                   
grant EXECUTE on sde.ST_DISJOINT to  app_user;                                    
grant EXECUTE on sde.ST_DISTANCE to  app_user;                                    
grant EXECUTE on sde.ST_DOMAIN_METHODS to  app_user;                              
grant EXECUTE on sde.ST_DOMAIN_OPERATORS to  app_user;                            
grant EXECUTE on sde.ST_DOMAIN_STATS to  app_user;                                
grant EXECUTE on sde.ST_ENDPOINT to  app_user;                                    
grant EXECUTE on sde.ST_ENTITY to  app_user;                                      
grant EXECUTE on sde.ST_ENVELOPE to  app_user;                                    
grant EXECUTE on sde.ST_ENVINTERSECTS to  app_user;                               
grant EXECUTE on sde.ST_EQUALS to  app_user;                                      
grant EXECUTE on sde.ST_EXTERIORRING to  app_user;                                
grant DEBUG on sde.ST_FUNCS_ARRAY to  app_user;                                   
grant EXECUTE on sde.ST_FUNCS_ARRAY to  app_user;                                 
grant EXECUTE on sde.ST_GEOMCOLLECTION to  app_user;                              
grant EXECUTE on sde.ST_GEOMETRY to  app_user;                                    
grant EXECUTE on sde.ST_GEOMETRYN to  app_user;                                   
grant EXECUTE on sde.ST_GEOMETRYTYPE to  app_user;                                
grant SELECT on sde.ST_GEOMETRY_COLUMNS to  app_user;                             
grant UPDATE on sde.ST_GEOMETRY_INDEX to  app_user;                               
grant SELECT on sde.ST_GEOMETRY_INDEX to  app_user;                               
grant INSERT on sde.ST_GEOMETRY_INDEX to  app_user;                               
grant DELETE on sde.ST_GEOMETRY_INDEX to  app_user;                               
grant EXECUTE on sde.ST_GEOMETRY_OPERATORS to  app_user;                          
grant EXECUTE on sde.ST_GEOMETRY_SHAPELIB_PKG to  app_user;                       
grant EXECUTE on sde.ST_GEOM fromTEXT to  app_user;                                
grant EXECUTE on sde.ST_GEOM fromWKB to  app_user;                                 
grant EXECUTE on sde.ST_GEOM_COLS_UTIL to  app_user;                              
grant EXECUTE on sde.ST_GEOM_UTIL to  app_user;                                   
grant EXECUTE on sde.ST_INTERIORRINGN to  app_user;                               
grant EXECUTE on sde.ST_INTERSECTION to  app_user;                                
grant EXECUTE on sde.ST_INTERSECTS to  app_user;                                  
grant EXECUTE on sde.ST_IS3D to  app_user;                                        
grant EXECUTE on sde.ST_ISCLOSED to  app_user;                                    
grant EXECUTE on sde.ST_ISEMPTY to  app_user;                                     
grant EXECUTE on sde.ST_ISMEASURED to  app_user;                                  
grant EXECUTE on sde.ST_ISRING to  app_user;                                      
grant EXECUTE on sde.ST_ISSIMPLE to  app_user;                                    
grant EXECUTE on sde.ST_LENGTH to  app_user;                                      
grant EXECUTE on sde.ST_LINE fromTEXT to  app_user;                                
grant EXECUTE on sde.ST_LINE fromWKB to  app_user;                                 
grant EXECUTE on sde.ST_LINESTRING to  app_user;                                  
grant EXECUTE on sde.ST_M to  app_user;                                           
grant EXECUTE on sde.ST_MAXM to  app_user;                                        
grant EXECUTE on sde.ST_MAXX to  app_user;                                        
grant EXECUTE on sde.ST_MAXY to  app_user;                                        
grant EXECUTE on sde.ST_MAXZ to  app_user;                                        
grant EXECUTE on sde.ST_MINM to  app_user;                                        
grant EXECUTE on sde.ST_MINX to  app_user;                                        
grant EXECUTE on sde.ST_MINY to  app_user;                                        
grant EXECUTE on sde.ST_MINZ to  app_user;                                        
grant EXECUTE on sde.ST_MLINE fromTEXT to  app_user;                               
grant EXECUTE on sde.ST_MLINE fromWKB to  app_user;                                
grant EXECUTE on sde.ST_MPOINT fromTEXT to  app_user;                              
grant EXECUTE on sde.ST_MPOINT fromWKB to  app_user;                               
grant EXECUTE on sde.ST_MPOLY fromTEXT to  app_user;                               
grant EXECUTE on sde.ST_MPOLY fromWKB to  app_user;                                
grant EXECUTE on sde.ST_MULTICURVE to  app_user;                                  
grant EXECUTE on sde.ST_MULTILINESTRING to  app_user;                             
grant EXECUTE on sde.ST_MULTIPOINT to  app_user;                                  
grant EXECUTE on sde.ST_MULTIPOLYGON to  app_user;                                
grant EXECUTE on sde.ST_MULTISURFACE to  app_user;                                
grant EXECUTE on sde.ST_NUMGEOMETRIES to  app_user;                               
grant EXECUTE on sde.ST_NUMINTERIORRING to  app_user;                             
grant EXECUTE on sde.ST_NUMPOINTS to  app_user;                                   
grant EXECUTE on sde.ST_ORDERINGEQUALS to  app_user;                              
grant EXECUTE on sde.ST_OVERLAPS to  app_user;                                    
grant UPDATE on sde.ST_PARTITION_INDEX to  app_user;                              
grant SELECT on sde.ST_PARTITION_INDEX to  app_user;                              
grant DELETE on sde.ST_PARTITION_INDEX to  app_user;                              
grant INSERT on sde.ST_PARTITION_INDEX to  app_user;                              
grant EXECUTE on sde.ST_POINT to  app_user;                                       
grant EXECUTE on sde.ST_POINT fromTEXT to  app_user;                               
grant EXECUTE on sde.ST_POINT fromWKB to  app_user;                                
grant EXECUTE on sde.ST_POINTN to  app_user;                                      
grant EXECUTE on sde.ST_POINTONSURFACE to  app_user;                              
grant EXECUTE on sde.ST_POLY fromTEXT to  app_user;                                
grant EXECUTE on sde.ST_POLY fromWKB to  app_user;                                 
grant EXECUTE on sde.ST_POLYGON to  app_user;                                     
grant EXECUTE on sde.ST_RELATE to  app_user;                                      
grant EXECUTE on sde.ST_RELATION_OPERATORS to  app_user;                          
grant EXECUTE on sde.ST_SPATIAL_INDEX to  app_user;                               
grant SELECT on sde.ST_SPATIAL_REFERENCES to  app_user;                           
grant EXECUTE on sde.ST_SPREF_UTIL to  app_user;                                  
grant EXECUTE on sde.ST_SRID to  app_user;                                        
grant EXECUTE on sde.ST_STARTPOINT to  app_user;                                  
grant EXECUTE on sde.ST_SURFACE to  app_user;                                     
grant EXECUTE on sde.ST_SYMMETRICDIFF to  app_user;                               
grant EXECUTE on sde.ST_TOUCHES to  app_user;                                     
grant EXECUTE on sde.ST_TRANSFORM to  app_user;                                   
grant EXECUTE on sde.ST_TYPE_EXPORT to  app_user;                                 
grant EXECUTE on sde.ST_TYPE_USER to  app_user;                                   
grant EXECUTE on sde.ST_TYPE_UTIL to  app_user;                                   
grant EXECUTE on sde.ST_UNION to  app_user;                                       
grant EXECUTE on sde.ST_VERIFY to  app_user;                                      
grant EXECUTE on sde.ST_WITHIN to  app_user;                                      
grant EXECUTE on sde.ST_X to  app_user;                                           
grant EXECUTE on sde.ST_Y to  app_user;                                           
grant EXECUTE on sde.ST_Z to  app_user;                                           
grant EXECUTE on sde.SVR_CONFIG_UTIL to  app_user;                                
grant SELECT on sde.TABLE_ID_GENERATOR to  app_user;                              
grant SELECT on sde.TABLE_LOCKS to  app_user;                                     
grant SELECT on sde.TABLE_REGISTRY to  app_user;                                  
grant UPDATE on sde.USER_ST_GEOMETRY_COLUMNS_V to  app_user;                      
grant SELECT on sde.USER_ST_GEOMETRY_COLUMNS_V to  app_user;                      
grant INSERT on sde.USER_ST_GEOMETRY_COLUMNS_V to  app_user;                      
grant DELETE on sde.USER_ST_GEOMETRY_COLUMNS_V to  app_user;                      
grant UPDATE on sde.USER_ST_GEOM_INDEX_V to  app_user;                            
grant SELECT on sde.USER_ST_GEOM_INDEX_V to  app_user;                            
grant INSERT on sde.USER_ST_GEOM_INDEX_V to  app_user;                            
grant DELETE on sde.USER_ST_GEOM_INDEX_V to  app_user;                            
grant SELECT on sde.VERSION to  app_user;                                         
grant SELECT on sde.VERSIONS to  app_user;                                        
grant SELECT on sde.VERSION_ID_GENERATOR to  app_user;                            
grant EXECUTE on sde.VERSION_USER_DDL to  app_user;                               
grant EXECUTE on sde.VERSION_UTIL to  app_user;                                   
grant SELECT on sde.XML_COLUMN_ID_GENERATOR to  app_user;                         
grant SELECT on sde.XML_DOC1_ID_GENERATOR to  app_user;                           
grant SELECT on sde.XML_DOC2_ID_GENERATOR to  app_user;                           
grant SELECT on sde.XML_DOC3_ID_GENERATOR to  app_user;                           
grant SELECT on sde.XML_DOC4_ID_GENERATOR to  app_user;                           
grant SELECT on sde.XML_INDEX_ID_GENERATOR to  app_user;                          
grant SELECT on sde.XML_TAG_ID_GENERATOR to  app_user;                            
grant EXECUTE on sde.XML_UTIL to  app_user;                                       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s