Oracle imp分区表,分区表迁移导入(table_exists_action)测试

有些项目中可能会涉及到表的分区的迁移，

下面简单写一下创建分区表过程

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参数对分区表效果和普通表有区别，我们测试②和③ 的结果，充分说明此观点有效。