ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-14060的解决

ORA-14060的解决

原创 Linux操作系统 作者:zhouxianwang 时间:2012-07-18 13:26:02 0 删除 编辑

           作者 :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

— The End —

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27036311/viewspace-735921/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-04

  • 博文量
    138
  • 访问量
    449107