failed to read USER_SDO_GEOM_METADATA view
650828Dec 5 2008 — edited Dec 12 2008Hello,
I'm trying to add a geometry column to an oracleXE table.
commands issued are:
1) alter table specimen add (geom mdsys.sdo_geometry);
2) update specimen set geom = mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(dec_long,dec_lat,null),null,null);
3) insert into mdsys.user_sdo_geom_metadata (table_name,column_name,diminfo,srid) values ('specimen','geom',mdsys.sdo_dim_array(mdsys.sdo_dim_element('longitude',-180,180,1), mdsys.sdo_dim_element ('latitude',-90,90,1)),8307);
all above seem to work correctly and if I list user_sdo_geom_metadata I get:
TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
SRID
----------
SPECIMEN
GEOM
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('longitude', -180, 180, 1), SDO_DIM_ELEMENT('latit
ude', -90, 90, 1))
8307
TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
SRID
----------
which also looks correct.
However, when I try to create a spatial index with:
create index specimen_sidx on specimen(geom) indextype is mdsys.spatial_index;
I get the error:
ERROR at line 1:
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
Does anyone have a fix or any idea why it fails?
Any help appreciated,
Dave