|
--把kingstar.emp表的数据迁移到kingstar.EMP_partition分区表中
--创建数据源表结构
drop table kingstar.emp;
create table kingstar.emp as select * from scott.emp where 1=2;
alter table kingstar.EMP
add constraint PK_EMP primary key (EMPNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
truncate table kingstar.emp; |
--插入数据
insert into kingstar.emp values (1,' 蔡浪','D',0,to_date('20080801 12:22:13','yyyymmdd hh24:mi:ss'),1000,1000,10);
insert into kingstar.emp values (2,' 蔡星','D',1,to_date('20080901 13:23:12','yyyymmdd hh24:mi:ss'),5000,5000,10);
commit; |
--查看迁移表中的数据
select * from kingstar.emp;
1 1 蔡浪 D 0 2008-8-1 12:22:13 1000.00 1000.00 10
2 2 蔡星 D 1 2008-9-1 13:23:12 5000.00 5000.00 10 |
--创建分区表 结构与kingstar.emp一样
-- Create table
create table kingstar.EMP_partition
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
tablespace KINGSTAR
partition by range(hiredate)
(partition emp_max values less than(maxvalue))--由于交换数据时需指定分区，所以这里不做详细分区，数据交换完后进行分区的拆分
; |
|
