samedi 5 mars 2016

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

Si lors d'un move de partition vous tombez sur l'erreur suivante "ORA-14257: cannot move partition other than a Range, List, System, or Hash partition", il est fort probable que vous avez essayé de déplacer des partitions qui contiennent des sous partitions. C'est ce qui m'est arrivé récemment lorsque j'ai voulu déplacer les partitions P_MINVAL et P_MAXVAL d'une des tables de mon client dans un autre tablepsace:
SQL> ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE PARTITION P_MAXVAL TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE PARTITION P_MAXVAL TABLESPACE USERS_LC32_DATA01 
                                                          * 
ERROR at line 1: 
ORA-14257: cannot move partition other than a Range, List, System, or Hash 
partition
Je me suis alors rendu compte que cette table avait des sous partitions et j'ai donc généré grâce à la requête suivante les commandes qui vont faire le move de toutes les sous partitions de la table CLIENT_DATA_DETAILS_PART:
select 'ALTER TABLE FORCE.' || TABLE_NAME || ' MOVE SUBPARTITION ' || SUBPARTITION_NAME || ' TABLESPACE USERS_LC32_DATA01;' 
FROM dba_tab_subpartitions 
where table_owner = 'FORCE' 
and table_name = ('CLIENT_DATA_DETAILS_PART') 
and partition_name in ('P_MINVAL', 'P_MAXVAL'); 
 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_ASIA TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_US TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_EUROPE TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_EUROPE_4 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_EUROPE_5 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MAXVAL_EUROPE_6 TABLESPACE USERS_LC32_DATA01; 
............................... 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_15 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_16 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_17 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_18 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_19 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_20 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_21 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_22 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_23 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_24 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_25 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_26 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_27 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_28 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_29 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_EUROPE_30 TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE SUBPARTITION P_MINVAL_OTHER TABLESPACE USERS_LC32_DATA01; 
Si mes sous partitions se trouvent désormais dans le tablespace USERS_LC32_DATA01 comme je le souhaitais j'ai toujours les partitions mères de ces sous partitions qui pointent dans le mauvais tablespace et bien sûr il est toujours impossible de faire un move de ces partitions:
SQL> select distinct tablespace_name 
  2  FROM dba_tab_subpartitions 
  3  where table_owner = 'FORCE' 
  4  and table_name = ('CLIENT_DATA_DETAILS_PART') 
  5  and partition_name in ('P_MINVAL', 'P_MAXVAL'); 
 
TABLESPACE_NAME 
------------------------------ 
USERS_LC32_DATA01 
 
SQL> select tablespace_name 
  2  FROM dba_tab_partitions 
  3  WHERE table_owner = 'FORCE' AND table_name in ('CLIENT_DATA_DETAILS_PART') 
  4   AND partition_name in ('P_MINVAL','P_MAXVAL'); 
 
TABLESPACE_NAME 
------------------------------ 
FORCE_HP32_CURRENT1 
FORCE_HP32_CURRENT1 
 
SQL> ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE PARTITION P_MAXVAL TABLESPACE USERS_LC32_DATA01; 
ALTER TABLE FORCE.CLIENT_DATA_DETAILS_PART MOVE PARTITION P_MAXVAL TABLESPACE USERS_LC32_DATA01 
                                                          * 
ERROR at line 1: 
ORA-14257: cannot move partition other than a Range, List, System, or Hash 
partition 
Je souhaiterais pourtant que ces partitions pointent sur le tablespace USERS_LC32_DATA01 et non pas FORCE_HP32_CURRENT1.
La solution consiste simplement à modifier le DEFAULT ATTRIBUTES pour ces partitions:
alter table FORCE.CLIENT_DATA_DETAILS_PART modify default attributes for partition P_MINVAL tablespace USERS_LC32_DATA01; 
alter table FORCE.CLIENT_DATA_DETAILS_PART modify default attributes for partition P_MAXVAL tablespace USERS_LC32_DATA01; 
 
SQL> alter table FORCE.CLIENT_DATA_DETAILS_PART modify default attributes for partition P_MAXVAL tablespace USERS_LC32_DATA01; 
 
Table altered. 
 
SQL> alter table FORCE.CLIENT_DATA_DETAILS_PART modify default attributes for partition P_MINVAL tablespace USERS_LC32_DATA01; 
 
Table altered. 
    
SQL> select tablespace_name 
  2  FROM dba_tab_partitions 
  3  WHERE table_owner = 'FORCE' AND table_name in ('CLIENT_DATA_DETAILS_PART') 
  4   AND partition_name in ('P_MINVAL','P_MAXVAL'); 
 
TABLESPACE_NAME 
------------------------------ 
USERS_LC32_DATA01 
USERS_LC32_DATA01
Comme vous pouvez le constater, mes partitions pointent désormais bien sur le nouveau tablespace USERS_LC32_DATA01.