| ORA-14060的解决 | DBA之路：OoNiceDream |

ORA-14060的解决

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/ora-14060.html

由于业务逻辑的改变，需要增加分区，分区键想由VARCHAR2(1)改为VARCHAR2(2)。报了个：

ORA-14060: data type or length of a table partitioning column may not be changed

解决方法挺简单的，就是使用exchange partition。

简单模拟如下：

SQL> create table LIST_PAR_TBL
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  )
  6  partition by list (PARTITION_ID)
  7  ( partition P01 values ('1'),
  8    partition P02 values ('2'),
  9    partition P03 values ('3'),
 10    partition P04 values ('4'),
 11    partition P05 values ('5'),
 12    partition P06 values ('6')
 13  );
 
Table created.
 
SQL> insert into LIST_PAR_TBL values('1','p1');
 
1 row created.
 
SQL> insert into LIST_PAR_TBL values('2','p2');
 
1 row created.
 
SQL> insert into LIST_PAR_TBL values('3','p3');
 
1 row created.
 
SQL> insert into LIST_PAR_TBL values('4','p4');
 
1 row created.
SQL> insert into LIST_PAR_TBL values('5','p5');
 
1 row created.
 
SQL> insert into LIST_PAR_TBL values('6','p6');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from LIST_PAR_TBL partition(p01);
 
P NAME
- --------------
1 p1
 
SQL> select * from LIST_PAR_TBL partition(p06);
 
P NAME
- --------------
6 p6
 
SQL> alter table LIST_PAR_TBL modify PARTITION_ID VARCHAR2(2);
alter table LIST_PAR_TBL modify PARTITION_ID VARCHAR2(2)
                                *
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be changed

接下来就是针对各个分区，创建结构相同的表，进行exchange:

SQL> create table LIST_PAR_TBL_P01
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
Table created.
 
SQL> create table LIST_PAR_TBL_P02
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
Table created.
 
SQL> create table LIST_PAR_TBL_P03
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
 
Table created.
 
SQL> SQL> create table LIST_PAR_TBL_P04
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
 
Table created.
 
SQL> SQL> create table LIST_PAR_TBL_P05
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
 
Table created.
 
SQL> SQL> create table LIST_PAR_TBL_P06
  2  (
  3    PARTITION_ID       VARCHAR2(1) not null,
  4    NAME               VARCHAR2(14)
  5  );
 
Table created.
 
SQL> 
 
SQL> alter table LIST_PAR_TBL exchange partition P01 with table  LIST_PAR_TBL_P01;
 
Table altered.
 
SQL> select * from LIST_PAR_TBL_P01;
 
P NAME
- --------------
1 p1
 
SQL> select * from LIST_PAR_TBL partition (P01);
 
no rows selected
 
SQL> 
 
SQL> alter table LIST_PAR_TBL exchange partition P02 with table  LIST_PAR_TBL_P02;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P03 with table  LIST_PAR_TBL_P03;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P04 with table  LIST_PAR_TBL_P04;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P05 with table  LIST_PAR_TBL_P05;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P06 with table  LIST_PAR_TBL_P06;
 
Table altered.
 
SQL> 
SQL> select * from LIST_PAR_TBL;
 
no rows selected

重建分区表，修改相应字段，再将数据exchange回来：

SQL> drop table LIST_PAR_TBL;
 
Table dropped.
 
SQL> create table LIST_PAR_TBL
  2  (
  3    PARTITION_ID       VARCHAR2(2) not null,
  4    NAME               VARCHAR2(14)
  5  )
  6  partition by list (PARTITION_ID)
  7  ( partition P01 values ('1'),
  8    partition P02 values ('2'),
  9    partition P03 values ('3'),
 10    partition P04 values ('4'),
 11    partition P05 values ('5'),
 12    partition P06 values ('6')
 13  );
 
Table created.
 
SQL> alter table LIST_PAR_TBL_P01 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P01 with table  LIST_PAR_TBL_P01;
 
Table altered.
 
SQL> select * from LIST_PAR_TBL partition(p01);
 
PA NAME
-- --------------
1  p1
 
SQL> select * from LIST_PAR_TBL_P01;
 
no rows selected
 
SQL> alter table LIST_PAR_TBL_P02 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL_P03 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL_P04 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL_P05 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL_P06 modify PARTITION_ID varchar2(2);
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P02 with table  LIST_PAR_TBL_P02;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P03 with table  LIST_PAR_TBL_P03;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P04 with table  LIST_PAR_TBL_P04;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P05 with table  LIST_PAR_TBL_P05;
 
Table altered.
 
SQL> alter table LIST_PAR_TBL exchange partition P06 with table  LIST_PAR_TBL_P06;
 
Table altered.
 
SQL> select * from LIST_PAR_TBL;
 
PA NAME
-- --------------
1  p1
2  p2
3  p3
4  p4
5  p5
6  p6
 
6 rows selected.
 
SQL> select * from LIST_PAR_TBL_P06;
 
no rows selected
 
SQL> desc LIST_PAR_TBL;
 Name                 Null?    Type
 -------------------- -------- ------------------
 PARTITION_ID         NOT NULL VARCHAR2(2)
 NAME                          VARCHAR2(14)
 
SQL>

另外需要注意的是，exchange partition需要两个表的表结构相同，否则会报：

SQL> alter table LIST_PAR_TBL exchange partition P01 with table  LIST_PAR_TBL_P01;
alter table LIST_PAR_TBL exchange partition P01 with table  LIST_PAR_TBL_P01
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION