oracle 普通表交换分区,普通表转换成分区表的四种方法

1)  EXPDPIMPDP

采用逻辑导出导入很简单，首先在源库建立分区表，然后将数据导出，然后导入到新建的分区表即可

2)  Insert into

利用原表重建分区表,再insert into 分区表 select *　from 原表

3)  Exchange Partition(交换分区)

4)  DBMS_REDEFINITION(在线重定义)

简单介绍1、2两种的实验过程

普通表数据如下

conn hr/hr

SQL> select * from CUSTOMER99;

CUST_NAME                                   CUST_ID

---------------------------------------- ----------

1                                                 1

11                                               11

21                                               21

31                                               31

41                                               41

--Insert into的案例

SQL>CREATE TABLE customer199

( cust_name      varchar2(20)

, cust_id       NUMBER

)

PARTITION BY RANGE (cust_id)

( PARTITION q1t VALUES LESS THAN (10)

, PARTITION q2t VALUES LESS THAN (20)

, PARTITION q3t VALUES LESS THAN (30)

, PARTITION q4t VALUES LESS THAN (40)

, partition q5t VALUES LESS THAN(maxvalue)

);

SQL>insert into customer199 select * from CUSTOMER99;

SQL> select * from customer199 partition(q3t);

CUST_NAME                                   CUST_ID

---------------------------------------- ----------

21                                               21

--EXPDPIMPDP的案例

expdp hr/hr directory=DATA_PUMP_DIR dumpfile=CUSTOMER99.dmp tables=CUSTOMER99

CREATE TABLEcustomer_expdp

( cust_name      varchar2(20)

, cust_id       NUMBER

)

PARTITION BY RANGE (cust_id)

( PARTITION q11 VALUES LESS THAN (10)

, PARTITION q21 VALUES LESS THAN (20)

, PARTITION q31 VALUES LESS THAN (30)

, PARTITION q41 VALUES LESS THAN (40)

, partition q51 VALUES LESS THAN(maxvalue)

);

impdp hr/hr directory=DATA_PUMP_DIR dumpfile=CUSTOMER99.dmp remap_table=CUSTOMER99:customer_expdp table_exists_action=append

SQL> select * from customer_expdp partition(q31);

CUST_NAME                                   CUST_ID

---------------------------------------- ----------

21                                               21