One of the common errors faced by DBA’s/Oracle Spatial analysts is the following error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 10

the reason i am writing about it is to help the community and new analysts to Oracle spatial to fix this problem in speedy manner.

SO the solution:

before creating the spatial index you need to update(insert) in the USER_SDO_GEOM_METADATA view, this is required for each new layer being created.

USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user.the view contains information about, for example:

insert into MDSYS.user_sdo_geom_metadata

(table_name, column_name, diminfo, srid)

values (‘GEO_TST’,’SHAPE1′, sdo_dim_array(sdo_dim_element(‘X’,0,20,0.005), sdo_dim_element(‘Y’,0,20,0.005) ), null );

Also, make sure that when you create the index afterwards…the account you are using has explicit “CREATE TABLE” & “CREATE SEQUENCE” privileges granted to it. Otherwise you will receive the following errors:

ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRS_13A71$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE SEQUENCE “HR”.”MDRS_13A71$” ORDER START WITH 1 CACHE 100
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 10