ORA-14060的解決

zhouxianwang發表於2012-07-18

           作者 ：【轉載時請務必以超連結形式標明文章原始出處和作者資訊】
                    連結：   

 

     由於業務邏輯的改變，需要增加分割槽，分割槽鍵想由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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章