分区表分区 交换 普通表

[allen519]

create table emp_b1 as select * from emp where empno<7500;
create table emp_b2 as select * from emp where empno>=7500 and empno<7900;
create table emp_b3 as select * from emp where empno>=7900;

create table emp_partb(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
partition by range (empno)
( partition part1 values less than (7500) TABLESPACE p1,
partition part2 values less than (7900) TABLESPACE p2,
partition part3 values less than (maxvalue) TABLESPACE p3);



alter table emp_partb exchange partition part1 with table EMP_B1;
alter table emp_partb exchange partition part2 with table EMP_B2;
alter table emp_partb exchange partition part3 with table emp_b3;
-- 报错 14097 大小不匹配或者 列类型的不匹配

insert into emp_partb partition (part1) select * from emp_b1; 正常

[beyon2008]

顶，还是用显式的create方式更保险啊

[卖水果的net]

ctas 方式创建的表，迁移到分区表时，可以考虑加一下 without validation 选项。

[allen519]

找到原因 ， 确实ctas 方式建立的表 格式不一样 create table emp_b3( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); insert into emp_b1 select * from emp where empno<7500; insert into emp_b2 select * from emp where empno>=7500 and empno<7900; insert into emp_b3 select * from emp where empno>=7900; alter table emp_partb exchange partition part3 with table emp_b3; OK！！！