table_exists_action=append和table_exists_action=truncate
一、环境准备
1.1 192.168.1.22上创建single01.student表
SQL> select * from single01.student;
ID NAME
------------ --------------------------------------------------------------------
1 xiaoming
expdp导出该表
mkdir -p /backup/expdp0522
create or replace directory dir_dump as '/u01/app/oracle/exp';
expdp \"/ as sysdba\" directory=dir_dump dumpfile=u_single01_202205_%U.dump parallel=2 logfile=u_single01_202205.log schemas=single01
impdp在192.168.1.67中导入该表
export ORACLE_SID=jdedb1
impdp \"/ as sysdba\" directory=dir_dump dumpfile=u_single01_202205_%U.dump parallel=2 schemas=single01 logfile=u_single01_202205.log
在192.168.1.67目标端上,
SQL> select * from single01.student;
ID NAME
---------- --------------------
1 xiaoming
二、验证append参数
1.修改源库数据
SQL> select * from single01.student;
ID NAME
---------- --------------------
1 xiaomingming
2 xiaozhang
2.导出后导入:
impdp \"/ as sysdba\" directory=dir_dump dumpfile=u_student_01modify_202205_%U.dump parallel=2 tables=single01.student logfile=u_student_01_202205.log table_exists_action=append
在192.168.1.67目标端上,
SQL> select * from single01.student;
ID NAME
---------- --------------------
1 xiaoming
1 xiaomingming
2 xiaozhang
可以看见,对不impdp之前得数据,参数append不能分辨已有数据,而是直接在后面全表追加
三、验证truncate参数
1.修改源库数据
SQL> update single01.student set name='xiaozhangzhang' where id=2;
1 row updated.
SQL> insert into single01.student values(3,'xiaoli');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from single01.student;
ID NAME
---------- ----------------------------------------------------------------------
1 xiaomingming
2 xiaozhangzhang
3 xiaoli
2.导出后导入数据
expdp \"/ as sysdba\" directory=dir_dump dumpfile=u_student_02modify_202205_%U.dump parallel=2 logfile=u_student_02modify_202205.log tables=single01.student
impdp \"/ as sysdba\" directory=dir_dump dumpfile=u_student_02modify_202205_01.dump parallel=2 tables=single01.student logfile=u_student_02_202205.log table_exists_action=truncate
此时在192.168.1.67目标端上检查
SQL> select * from single01.student;
ID NAME
---------- ----------------------------------------------------------------------
1 xiaomingming
2 xiaozhangzhang
3 xiaoli
truncate参数是删除目标端67上得原有数据,再将新数据导入