2

Trying to move table tab from tablespace oldTs to newTs. There is a composite partition (Range/Hash) on tab. Hence, a direct "Alter-Table-Move-Tablespace" query won't work, need to migrate partition by partition. Below is the SQL of tab:

CREATE TABLE tab
(
    col_1 char(6),
    col_2 varchar2(4),
    col_3 varchar2(5)
)
TABLESPACE oldTs PARTITION BY RANGE
(
    "col_1"
)
SUBPARTITION BY HASH
(
    "col_2"
)
SUBPARTITIONS 1
(
    PARTITION "P201102" VALUES LESS THAN ('201103') PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "oldTs" NOCOMPRESS NOLOGGING ( SUBPARTITION "SYS_SUBP5223" TABLESPACE "oldTs" NOCOMPRESS , SUBPARTITION "SYS_SUBP5224" TABLESPACE "oldTs" NOCOMPRESS ),
    PARTITION "P201103" VALUES LESS THAN ('201104') PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "oldTs" NOCOMPRESS NOLOGGING ( SUBPARTITION "SYS_SUBP5225" TABLESPACE "oldTs" NOCOMPRESS , SUBPARTITION "SYS_SUBP5226" TABLESPACE "oldTs" NOCOMPRESS )
);

There are several such monthly partitions, created a procedure which acquires all the partition names (such as P201102, P2001103) and generates an alter query for moving partitions. For ex, 

ALTER TABLE tab_name MOVE PARTITION P201102 TABLESPACE newTbs;

But, the query gives below error:

SQL Error: ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

Also, if you notice in P201102/P201103, each has two sub-partitions (SYS_SUBP5225 & SYS_SUBP5226 for P201103).

Require the correct syntax of alter statement for migrating partitions for the above scenario.

Lastwish
  • 317
  • 10
  • 21

1 Answers1

2

You can move subpartitions but not partitions which contains subpartitions. (Shortly, you can move segments.)

ALTER TABLE tab_name MOVE SUBPARTITION SYS_SUBP5225 TABLESPACE newTbs;

UPDATE: If you want to change where the new partitions are created then run the bellow ddl, which change an atribute of the table - where new partitions are created. 

 alter table tab_name modify default attributes tablespace newTbs;

UPDATE2: If you want to change where subpartitions are created for a partition, then run below which change an attribute of the partition - where new subpartitions are created:

alter table tab_name modify default attributes for partition P201102 tablespace newTbs;
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
  • Already tried doing that, moved two subpartitions, like: `ALTER TABLE tab_name MOVE SUBPARTITION SYS_SUBP5225 TABLESPACE newTbs;` `ALTER TABLE tab_name MOVE SUBPARTITION SYS_SUBP5226 TABLESPACE newTbs;` But, this only changed the subpartition's tablespace, the old tablespace was still reflecting: `PARTITION "P201103" VALUES LESS THAN ('201104')...TABLESPACE "**oldTs**" NOCOMPRESS NOLOGGING( SUBPARTITION "SYS_SUBP5225" TABLESPACE "newTs" NOCOMPRESS ,SUBPARTITION "SYS_SUBP5226" TABLESPACE "newTs" NOCOMPRESS )` – Lastwish Dec 04 '15 at 13:25
  • Didn't quite understand what this does, can you edit with an example ? – Lastwish Dec 04 '15 at 13:40
  • It just change an attribute of the table. New partititions will be created there. If you want to move an exsiting partition, then you should use `move`. – Florin Ghita Dec 04 '15 at 13:44
  • Got it. But, even after moving the subpartitions, the old tablespace **oldTs**, is still present : `PARTITION "P201103" VALUES LESS THAN ('201104')...TABLESPACE "**oldTs**" NOCOMPRESS NOLOGGING( SUBPARTITION "SYS_SUBP5225" TABLESPACE "newTs" NOCOMPRESS ,SUBPARTITION "SYS_SUBP5226" TABLESPACE "newTs" NOCOMPRESS )` – Lastwish Dec 04 '15 at 13:46
  • My apologies for the delayed response. Latest updated query works fine, now is there any way to dynamically acquire all the system generated subpartition names ? Such as : SYS_SUBP5226, SYS_SUBP5225... – Lastwish Dec 07 '15 at 09:57
  • 1
    Sure, that's simple. select * from all/dba/user_segments with desired filters. – Florin Ghita Dec 07 '15 at 09:59
  • Tried using all/dba_user_segments, didn't contain all the subpartitions for some reason...but, ALL_TAB_SUBPARTITIONS does and worked fine,.. thank you for your help...wanted to understand a bit, can you please elaborate on what below query does ? `ALTER TABLE TAB MODIFY DEFAULT ATTRIBUTES TABLESPACE newTs` `Alter table tab MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201102 newTs` And does the sub-partition contain all the records ? – Lastwish Dec 10 '15 at 13:42