OraFAQ Forum: Spatial » Move segments of a partitioned table containing SDO

Home » Server Options » Spatial » Move segments of a partitioned table containing SDO_GEOMETRY (11g)

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

Move segments of a partitioned table containing SDO_GEOMETRY [message #592218]

Mon, 05 August 2013 12:29

babak4
Messages: 1
Registered: August 2013
Location: London

Junior Member

Hi,

I need to move segments of a table containing SDO_Geometry to shrink a datafile, but I none of the command structure that I know of, work.

Table's name is X with partitions such as DAT_200906010000. X has a column named "Location" of type SDO_Geometry.

When I check DBA_EXTENTS (ordered by block_id desc) I get these:

Segment_name Partition_Name Segment_type Tablespace_name
------------- -------------- ------------ ---------------
SYS_LOB0003898574C00030$$ SYS_LOB_P697584 LOB PARTITION DATA_01

Through DBA_LOB_PARTITIONS I find the associated table_name, Column_name, and table partition name

TABLE_NAME COLUMN_NAME PARTITION_NAME LOB_INDPART_NAME TABLESPACE_NAME
---------- ----------- -------------- ---------------- ---------------
X "LOCATION"."SDO_ELEM_INFO" DAT_200906010000 SYS_IL_P697619 DATA_01

and using DBA_LOBS, I find the index name for the lob (on the same tablespace):

TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME
---------- ------------------------- ------------------------- ------------------------
X "LOCATION"."SDO_ELEM_INFO" SYS_LOB0003898574C00030$$ SYS_IL0003898574C00030$$
X "LOCATION"."SDO_ORDINATES" SYS_LOB0003898574C00031$$ SYS_IL0003898574C00031$$

1. alter table X move partition SYS_LOB_P697584 lob("LOCATION") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
Result: ORA-02149: Specified partition does not exist

2. alter table X move partition DAT_200906010000 lob("LOCATION") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
Result: ORA-22997: VARRAY | OPAQUE stored as LOB is not specified at the table level

3. alter table X move partition DAT_200906010000 SYS_LOB_P697584("LOCATION") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
Result: ORA-14020: this physical attribute may not be specified for a table partition

4. alter table X move partition DAT_200906010000 lob("LOCATION") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
Result: ORA-22997: VARRAY | OPAQUE stored as LOB is not specified at the table level

5. alter table X move partition DAT_200906010000 lob("LOCATION"."SDO_ELEM_INFO") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
Result: ORA-22917: use VARRAY to define the storage clause for this column or attribute

6. alter table X move partition DAT_200906010000 varray "LOCATION.sdo_elem_info" store as lob(tablespace DATA_01)
Result: ORA-00922: missing or invalid option

7. alter table X move partition DAT_200906010000 varray ("LOCATION.SDO_ELEM_INFO") store as lob(tablespace DATA_01)
Result: ORA-00931: missing identifier

8. alter table X move partition DAT_200906010000 varray "LOCATION.SDO_ELEM_INFO" store as lob(tablespace DATA_01) varray "LOCATION.SDO_ORDINATES" store as lob(tablespace DATA_01)
Result: ORA-00922: missing or invalid option

9. alter table X move partition DAT_200906010000 varray ("LOCATION.SDO_ELEM_INFO") store as lob(tablespace DATA_01) varray("LOCATION.SDO_ORDINATES") store as lob(tablespace DATA_01)
Result: ORA-00931: missing identifier

10. alter index SYS_IL0003898574C00030$$ rebuild partition SYS_LOB_P697584 (or SYS_IL_P697619)
Result: ORA-01418: specified index does not exist

11. alter table X move partition DAT_200906010000 [tablespace DATA_01]
varray("LOCATION.SDO_ELEM_INFO") store as SYS_LOB0003898574C00030$$(tablespace DATA_01)
varray("LOCATION.SDO_ORDINATES") store as SYS_LOB0003898574C00031$$(tablespace DATA_01);
Result: ORA-00931: missing identifier

I'd really appreciate it if anyone could let me know what am I doing wrong? or not doing?

Regards,
Babak.

Report message to a moderator