OraFAQ Forum: Spatial » How to move sdo

Home » Server Options » Spatial » How to move sdo_geometry type index? (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to move sdo_geometry type index? [message #548860]

Mon, 26 March 2012 13:29

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

Report message to a moderator

Re: How to move sdo_geometry type index? [message #548876 is a reply to message #548860]

Mon, 26 March 2012 15:56

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

Report message to a moderator

Re: How to move sdo_geometry type index? [message #548879 is a reply to message #548876]

Mon, 26 March 2012 18:32

Barbara Boehmer
Messages: 8988
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?

Report message to a moderator

Re: How to move sdo_geometry type index? [message #549024 is a reply to message #548879]

Tue, 27 March 2012 10:21

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

Report message to a moderator

Re: How to move sdo_geometry type index? [message #549045 is a reply to message #549024]

Tue, 27 March 2012 12:26

Barbara Boehmer
Messages: 8988
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);

Report message to a moderator

Re: How to move sdo_geometry type index? [message #549191 is a reply to message #549045]

Wed, 28 March 2012 09:40

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Hurray!!!! it works. Thanks a bunch.

you are awesome. Smile

Report message to a moderator

Previous Topic:	Select geometry column
Next Topic:	cant start oracleDBconsoleorcl service on my xp machine

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 22:13:25 CDT 2023