Re: ORA-29850: invalid option for creation of domain indexes

From: <karen.hedges_at_googlemail.com>
Date: 24 Jul 2006 05:34:39 -0700
Message-ID: <1153744479.022503.13310@h48g2000cwc.googlegroups.com>

Vladimir M. Zakharychev wrote:
> karen.hedges_at_googlemail.com wrote:
> > Hi,
> >
> > I am trying to create a spatial index in a specific tablespace with
> > Oracle 10G but keep getting the following errors:
> >
> > SQL> CREATE INDEX DUMMY_GEOM_FBSIDX on DUMMY(DUMMY_GEOM) indextype is
> > mdsys.spat
> > ial_index tablespace DUMMYINDX;
> > CREATE INDEX DUMMY_GEOM_FBSIDX on DUMMY(DUMMY_GEOM) indextype is
> > mdsys.spatial_i
> > ndex tablespace DUMMYINDX
> >
> > *
> > ERROR at line 1:
> > ORA-29850: invalid option for creation of domain indexes
> >
> > If I don't specify the tablespace, the spatial index is created as
> > shown below:
> >
> > SQL> CREATE INDEX DUMMY_GEOM_FBSIDX on DUMMY(DUMMY_GEOM) indextype is
> > mdsys.spat
> > ial_index;
> >
> > Index created.
> >
> > Just for reference, the spaces and carriage returns are just a part fo
> > cut and paste and the geometry object is setup as shown below:
> >
> > CREATE TABLE DUMMY (
> > DUMMY_GEOM MDSYS.SDO_GEOMETRY
> > ) tablespace DUMMYDATA;
> >
> >
> > INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO,
> > SRID)
> > VALUES ('DUMMY', 'DUMMY_GEOM',
> > MDSYS.SDO_DIM_ARRAY
> > (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
> > MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)
> > ),
> > 8307);
> >
> > TIA.
> >
> > K

>

> How about you read the Spatial docs and see that physical storage
> attributes for spatial indexes are supplied in PARAMETERS('') clause of
> CREATE INDEX statement? Your statement should be:

>

> CREATE INDEX DUMMY_GEOM_FBSIDX
> on DUMMY(DUMMY_GEOM)
> indextype is mdsys.spatial_index
> PARAMETERS('tablespace DUMMYINDX')
> /

>


> Hth,

>     Vladimir M. Zakharychev

>     N-Networks, makers of Dynamic PSP(tm)

>     http://www.dynamicpsp.com


Thanks for that.  Just thought I'd mention that I had to add an equals
sign between tablespace and DUMMYINDX.  The revised version is thus:

CREATE INDEX DUMMY_GEOM_FBSIDX on DUMMY(DUMMY_GEOM) indextype is mdsys.spatial_index PARAMETERS('tablespace=DUMMYINDX'); Received on Mon Jul 24 2006 - 07:34:39 CDT