Oracle Database Discussions

I am having problem with subpartitioned table move to different tbs. Oracle 10g

I understand that if I have table that is range partitioned and each partition is list subpartitioned, I can only move subpartitions to different tablespace.

ALTER TABLE OWNER.STATS_TEST MOVE SUBPARTITION SPCS_2010_RP TABLESPACE NEW_TBS;


Attempting to move partition [parent of above subpartition] will throw an error:

ALTER TABLE OWNER.STATS_TEST MOVE PARTITION PCS_2010 TABLESPACE NEW_TBS;
ORA-14257: cannot move partition other than a Range or Hash partition

This is Oracle documented fact. You cannot move partitions if they contain subpartitions, because the actual segment is subpartition.


Now, I can change the default tablespace of a table:
ALTER TABLE OWNER.STATS_TEST MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TBS;

From now on, all the new created partitions will go to NEW_TBS tbs;


But what to do with the left-behind partition which still resides in old tbs ? How to move it to NEW_TBS ??? It doesn't actually reside in old tbs [this is not segment - subpartition is] but the dictionary [dba_tab_partitions] says it still belongs to old tb. If I removed old tbs, entry will still be there. Any hints please?