Wednesday, February 13, 2013

ORA-14257 - One of the misleading error message.



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. 

 

1 comment: