作者 :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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27036311/viewspace-735921/