| 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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
