12C新特性：impdp不写入redo日志，如果数据库开启force logging则无效。
1、查看数据库配置
[oracle@cat1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 4 08:47:32 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA1
Oldest online log sequence 15
Next log sequence to archive 16
Current log sequence 16
SQL> select FORCE_LOGGING from v$database;
FORCE_LOGGING
--------------------------------------------------------------------------------
NO2、登录PDB创建测试数据
[oracle@cat1 ~]$ sqlplus test/test@PDB1_WHS
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 4 15:04:17 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Dec 04 2020 08:46:50 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table t_whs as select * from dba_objects;
Table created.
SQL> insert into t_whs select * from t_whs;
72474 rows created.
SQL> insert into t_whs select * from t_whs;
144948 rows created.
SQL> insert into t_whs select * from t_whs;
289896 rows created.
SQL> insert into t_whs select * from t_whs;
579792 rows created.
SQL> insert into t_whs select * from t_whs;
1159584 rows created.
SQL> /
2319168 rows created.
SQL> /
4638336 rows created.
SQL> commit;
Commit complete.
SQL> select *from (
2 select dest_id,thread#,sequence#
3 from v$archived_log order by completion_time desc)
4 where rownum<=5;
DEST_ID THREAD# SEQUENCE#
---------- ---------- ----------
1 1 24
1 1 23
1 1 22
1 1 21
1 1 20
SQL> create directory dumpdir as '/tmp';
Directory created.
SQL> 3、导出数据
[oracle@cat1 tmp]$ expdp test/test@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs
Export: Release 19.0.0.0.0 - Production on Fri Dec 4 15:21:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T_WHS" 1.192 GB 9276672 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/tmp/t_whs.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 4 15:22:12 2020 elapsed 0 00:00:224、普通方式导入数据
删除表后，查看日志序号
SQL> drop table t_whs;
Table dropped.
SQL> select *from (
2 select dest_id,thread#,sequence#
3 from v$archived_log order by completion_time desc)
4 where rownum<=5;
DEST_ID THREAD# SEQUENCE#
---------- ---------- ----------
1 1 24
1 1 23
1 1 22
1 1 21
1 1 20普通方式导入数据
[oracle@cat1 tmp]$ impdp test/test@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs
Import: Release 19.0.0.0.0 - Production on Fri Dec 4 15:25:11 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/********@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_WHS" 1.192 GB 9276672 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 4 15:25:45 2020 elapsed 0 00:00:33SQL> select *from (
2 select dest_id,thread#,sequence#
3 from v$archived_log order by completion_time desc)
4 where rownum<=5;
DEST_ID THREAD# SEQUENCE#
---------- ---------- ----------
1 1 31 <==24变为31增加了7个日志
1 1 30
1 1 29
1 1 28
1 1 275、dsiable_archive_loggin方式导入数据
SQL> drop table t_whs;
Table dropped.
SQL> select *from (
2 select dest_id,thread#,sequence#
3 from v$archived_log order by completion_time desc)
4 where rownum<=5;
DEST_ID THREAD# SEQUENCE#
---------- ---------- ----------
1 1 31
1 1 30
1 1 29
1 1 28
1 1 27
[oracle@cat1 tmp]$ impdp test/test@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Import: Release 19.0.0.0.0 - Production on Fri Dec 4 15:31:48 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/********@PDB1_WHS directory=dumpdir dumpfile=t_whs.dmp logfile=dump.log tables=t_whs TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_WHS" 1.192 GB 9276672 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 4 15:31:55 2020 elapsed 0 00:00:06SQL> select *from (
2 select dest_id,thread#,sequence#
3 from v$archived_log order by completion_time desc)
4 where rownum<=5;
DEST_ID THREAD# SEQUENCE#
---------- ---------- ----------
1 1 31 <==还是31没有增加
1 1 30
1 1 29
1 1 28
1 1 27同样的数据量，dsiable_archive_loggin方式导入数据只用了6秒，普通方式用了33秒，大大提高了导入效率。