Home » Server Options » Spatial » How to move sdo_geometry type index? (Oracle 11g)
How to move sdo_geometry type index? [message #548860] Mon, 26 March 2012 13:29 Go to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Hi,

Iam running the following command to move LOB index to another table space.

ALTER TABLE TEST MOVE LOB("GEOM"."SDO_ELEM_INFO") STORE AS (TABLESPACE USERS);

I get the following error ORA-22917: use VARRAY to define the storage clause for this column or attribute

Action: Specify VARRAY before the colun storage caluse and resubmit statement.

Note: GEOM column is of type SDO_GEOMETRY.

Can some send me the correct syntax to move the column to different table space for a column of type SDO_GEOMETRY?

Thanks
Re: How to move sdo_geometry type index? [message #548876 is a reply to message #548860] Mon, 26 March 2012 15:56 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
I was able to figure out the syntax Here it is

ALTER TABLE TABLE_NAME MOVE TABLESPACE CURRENT_TABLESPACE VARRAY COLUMN.SDO_ELEM_INFO STORE AS LOB(TABLESPACE NEW_TABLESPACE)

when I execute this statement it successfully executed but a minute later the index is back to the old tablespace.

Iam not sure why it is reverting back to the old tablespace.

Any thoughts?

Thanks
Re: How to move sdo_geometry type index? [message #548879 is a reply to message #548876] Mon, 26 March 2012 18:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Can you post a reproducible test case that causes the tablespace to revert back and show how you are determining that?
Re: How to move sdo_geometry type index? [message #549024 is a reply to message #548879] Tue, 27 March 2012 10:21 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
I have column called GEOM which is of type SDO_GEOMETRY In table TEST and has indexes on this column.

I ran the following command to move the indexes from GEO_TEMP to users table space.

ALTER TABLE TEST MOVE TABLESPACE GEO_TEMP VARRAY "GEOM.SDO_ELEM_INFO" STORE AS LOB(TABLESPACE USERS);

I also ran the following statement because GEOM COLUMN displays two column names(sdo_ordinates and sdo_elem_info) in the user_lobs table.

ALTER TABLE TEST MOVE TABLESPACE GEO_TEMP VARRAY "GEOM.ORDINATES" STORE AS LOB(TABLESPACE USERS);

The above statements are executed fine without any error but when I check the tablespace on them is still in in the old tablespace(geom_temp) i.e when I execute statement for GEOM.ORDINATES then geom.sdo_elem_info comes back to geo_temp tablespace or when I execute geom.sdo_elem_info statement then GEOM.ORDINDATES is back to ghe geo_temp table space.


Hope it is clear now.

Is there a better way to move the sdo_geometry data type indexes to another table space ?

Any info is greatly appreciated.

Thanks
Re: How to move sdo_geometry type index? [message #549045 is a reply to message #549024] Tue, 27 March 2012 12:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What happens if you put them both in one command, like below?

ALTER TABLE TEST MOVE TABLESPACE GEO_TEMP
VARRAY "GEOM.SDO_ELEM_INFO" STORE AS LOB(TABLESPACE USERS)
VARRAY "GEOM.ORDINATES" STORE AS LOB(TABLESPACE USERS);
Re: How to move sdo_geometry type index? [message #549191 is a reply to message #549045] Wed, 28 March 2012 09:40 Go to previous message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Hurray!!!! it works. Thanks a bunch.

you are awesome. Smile
Previous Topic: Select geometry column
Next Topic: cant start oracleDBconsoleorcl service on my xp machine
Goto Forum:
  


Current Time: Thu Mar 28 11:35:29 CDT 2024