Sometimes , you wonder why Oracle would give misleading error message .
This is reference to ORA-14257.
SQL Error: ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
14257. 00000 - "cannot move partition other than a Range or Hash partition"
*Cause: User attempt to move a partition that is not a Range or Hash
partition which is illegal
*Action: Specify MOVE PARTITION for a Range or Hash partition only
As part of database maintenance route , we may need to change the storage of some segments . In this case , we are moving the partition(s) to a different tablespace. When you a simple partitioned table , we need specify the move command at the partition level ; but if is a composite partitioned table , the move command should be at the sub-partition level. If you wrongfully specified the partition level move for a composite partitioned table , then Oracle throws ORA-14257 ( a misleading text).
Here is an example.
SQL> DROP TABLE storesales purge ;
Table dropped.
SQL> CREATE TABLE storesales
2 (
3 storekey INT NOT NULL ,
4 datekey INT NOT NULL ,
5 regionkey INT NOT NULL ,
6 saleqty INT NOT NULL ,
7 CONSTRAINT PK_storesales_sp PRIMARY KEY (datekey , regionkey , storekey )
8 )
9 PARTITION BY LIST ( datekey )
10 (
11 PARTITION P_2012 VALUES ( 12 )
12 , PARTITION P_2013 VALUES ( 13 )
13 ) ;
Table created.
SQL> SELECT table_name ,
2 composite ,
3 partition_name ,
4 tablespace_name
5 FROM user_tab_partitions
6 WHERE table_name ='STORESALES' ;
TABLE_NAME COM PARTITION_NAME TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES NO P_2012 USERS
STORESALES NO P_2013 USERS
SQL> alter table storesales move partition p_2012 tablespace tbscsb;
Table altered.
SQL> alter table storesales move partition p_2013 tablespace tbscsb;
Table altered.
SQL> SELECT table_name ,
2 composite ,
3 partition_name ,
4 tablespace_name
5 FROM user_tab_partitions
6 WHERE table_name ='STORESALES' ;
TABLE_NAME COM PARTITION_NAME TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES NO P_2012 TBSCSB
STORESALES NO P_2013 TBSCSB
SQL>
As we can see , this table is of simple partition (composite = NO ) the table partitions have been moved to "TBSCSB" ( different tablespace) with no issues.
Now , let us create a composite partitioned table and perform similar operations.
SQL> drop table storesales_cp purge ;
Table dropped.
SQL> Create table storesales_cp
2 ( storekey int not null
3 , datekey int not null
4 , regionkey int not null
5 , saleqty int not null
6 , CONSTRAINT PK_storesales_cp PRIMARY KEY (datekey , regionkey , storekey )
7 )
8 PARTITION BY LIST (datekey)
9 SUBPARTITION BY LIST (regionkey)
10 (
11 PARTITION P_2012 VALUES ( 12 )
12 ( SUBPARTITION P2012_0 VALUES (0) ,
13 SUBPARTITION P2012_1 VALUES (1) ,
14 SUBPARTITION P2012_7 VALUES (7) ,
15 SUBPARTITION P2012_9 VALUES (9)
16 )
17 ,
18 PARTITION P_2013 VALUES ( 13 )
19 ( SUBPARTITION P2013_0 VALUES (0) ,
20 SUBPARTITION P2013_1 VALUES (1) ,
21 SUBPARTITION P2013_7 VALUES (7) ,
22 SUBPARTITION P2013_9 VALUES (9)
23 )
24 ) ;
Table created.
SQL> SELECT table_name ,
2 composite ,
3 partition_name ,
4 tablespace_name
5 FROM user_tab_partitions
6 WHERE table_name ='STORESALES_CP' ;
TABLE_NAME COM PARTITION_NAME TABLESPACE_NAME
------------------------------ --- ------------------------------ ------------------------------
STORESALES_CP YES P_2012 USERS
STORESALES_CP YES P_2013 USERS
SQL> alter table storesales_cp move partition p_2012 tablespace tbscsb;
alter table storesales_cp move partition p_2012 tablespace tbscsb
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
Oracle should have provided me an error message that this is a composite partition but not the above.
Let us perform the move operations at the sub-partition level.
SQL> SELECT table_name ,
2 partition_name ,
3 subpartition_name ,
4 tablespace_name
5 FROM user_tab_subpartitions
6 WHERE table_name ='STORESALES_CP' ;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
STORESALES_CP P_2012 P2012_0 USERS
STORESALES_CP P_2012 P2012_1 USERS
STORESALES_CP P_2012 P2012_7 USERS
STORESALES_CP P_2012 P2012_9 USERS
STORESALES_CP P_2013 P2013_0 USERS
STORESALES_CP P_2013 P2013_1 USERS
STORESALES_CP P_2013 P2013_7 USERS
STORESALES_CP P_2013 P2013_9 USERS
8 rows selected.
SQL> alter table storesales_cp move subpartition p2012_0 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2012_1 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2012_7 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2012_9 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2013_0 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2013_1 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2013_7 tablespace tbscsb;
Table altered.
SQL> alter table storesales_cp move subpartition p2013_9 tablespace tbscsb;
Table altered.
SQL>
SQL> SELECT table_name ,
2 partition_name ,
3 subpartition_name ,
4 tablespace_name
5 FROM user_tab_subpartitions
6 WHERE table_name ='STORESALES_CP' ;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
STORESALES_CP P_2012 P2012_0 TBSCSB
STORESALES_CP P_2012 P2012_1 TBSCSB
STORESALES_CP P_2012 P2012_7 TBSCSB
STORESALES_CP P_2012 P2012_9 TBSCSB
STORESALES_CP P_2013 P2013_0 TBSCSB
STORESALES_CP P_2013 P2013_1 TBSCSB
STORESALES_CP P_2013 P2013_7 TBSCSB
STORESALES_CP P_2013 P2013_9 TBSCSB
8 rows selected.
SQL>
Now , the move operations are successful. I believe, in the upcoming release of Oracle ( Oracle 12c) , some of the partition management are made easier . Hopefully , Oracle changes the text of this message as well.
Comments welcome.
