有些项目中可能会涉及到表的分区的迁移，
下面简单写一下创建分区表过程
1、创建测试表
首先创建测试表weihai_test语句如下
create table test (
id int,
join_date DATE);
以上表中join_date字段为分区表字段
2、插入数据
2.1、模拟插入30万条数据
declare
i int := 1;
year VARCHAR2(20);
begin
loop
year :=CASE mod(i, 3)
WHEN 0 THEN
‘2019-01-01 00:00:00’
WHEN 1 THEN
‘2019-06-01 00:00:00’
ELSE
‘2019-12-01 00:00:00’
END;
insert into test values(i, to_date(year, ‘yyyy-mm-dd hh24:mi:ss’));
exit when i= 300000;
i := i+ 1;
end loop;
end;
/
commit;
2.2、查看是否插入成功
select count(1) from test;
3、创建分区表
create table test_p (
id int,
join_date DATE )
partition by range(join_date)
(
partition p1 values less than (to_date(‘2019-01-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)) tablespace DB_TS,
partition p2 values less than (to_date(‘2019-04-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)) tablespace DB_TS,
partition p3 values less than (to_date(‘2019-08-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)) tablespace DB_TS,
partition p4 values less than (to_date(‘2020-12-31 23:59:59’,‘yyyy-mm-ddhh24:mi:ss’)) tablespace DB_TS
);
4、数据转移
表创建完成之后，开始导数，
insert /+append/ test_p(
id,join_date) select id,join_date from test;
commit;
5、数据对比
导入完成之后，对比两张表的数据，一样就表示导入成功
select count(1) from test_p;
select count(1) from test;
重点：（导出语句此处省略）
我们测试的目的是table_exists_action参数对分区表作用
impdp tss/bd123 DIRECTORY=my_dir DUMPFILE=partition_p3.dmp logfile=imp.log table_exists_action=replace sqlfile=p3.sql
①导入：（目标端表对已存在，对分区表对应分区执行truncate，再执行导入）
先truncate
SQL> alter table test_p truncate partition(p3);
Table truncated.
导入前数据检查
SQL> select count(1) from test_p;
COUNT(1)
----------
220000
SQL>
SQL> select count(1) from test_p partition(p1);
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from test_p partition(p2);
COUNT(1)
----------
10000
SQL>
SQL> select count(1) from test_p partition(p3);
COUNT(1)
----------
0
执行导入
impdp tss/bd123 DIRECTORY=my_dir DUMPFILE=partition_p4.dmp logfile=imp.log table_exists_action=append;
SQL> select count(1) from test_p;
COUNT(1)
----------
340000
SQL>
SQL> select count(1) from test_p partition(p1);
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from test_p partition(p2);
COUNT(1)
----------
10000
SQL>
SQL> select count(1) from test_p partition(p3);
COUNT(1)
----------
120000
**②table_exists_action=truncate; **
SQL> select count(1) from weihai_test_p;
COUNT(1)
----------
460000
SQL>
SQL> select count(1) from weihai_test_p partition(p1);
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from weihai_test_p partition(p2);
COUNT(1)
----------
10000
SQL>
SQL> select count(1) from weihai_test_p partition(p3);
COUNT(1)
----------
240000
导入：impdp tss/bd123 DIRECTORY=my_dir DUMPFILE=partition_p4.dmp logfile=imp.log table_exists_action=truncate;
SQL> select count(1) from weihai_test_p;
COUNT(1)
----------
120000
SQL>
SQL> select count(1) from weihai_test_p partition(p1);
COUNT(1)
----------
0
SQL>
SQL> select count(1) from weihai_test_p partition(p2);
COUNT(1)
----------
0
SQL>
SQL> select count(1) from weihai_test_p partition(p3);
COUNT(1)
----------
120000
**③table_exists_action=replace; **
SQL> select count(1) from weihai_test_p;
COUNT(1)
----------
450000
SQL>
SQL> select count(1) from weihai_test_p partition(p1);
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from weihai_test_p partition(p2);
COUNT(1)
----------
10000
SQL>
SQL> select count(1) from weihai_test_p partition(p3);
COUNT(1)
----------
230000
导入：impdp tss/bd123 DIRECTORY=my_dir DUMPFILE=partition_p4.dmp logfile=imp.log **table_exists_action=replace **
SQL> select count(1) from weihai_test_p;
COUNT(1)
----------
120000
SQL>
SQL> select count(1) from weihai_test_p partition(p1);
COUNT(1)
----------
0
SQL>
SQL> select count(1) from weihai_test_p partition(p2);
COUNT(1)
----------
0
SQL>
SQL> select count(1) from weihai_test_p partition(p3);
COUNT(1)
----------
120000
总结：
通过测试我们会发现：对目标端已存在的分区表的部分分区执行导入时，建议对指定分区先执行truncate，再通过append参数导入指定分区。并且truncate、replace、append参数对分区表效果和普通表有区别，我们测试②和③ 的结果，充分说明此观点有效。
