在知识库中专注阅读，并随时返回相关工具与课程
原文地址：https://oracle-base.com/articles/21c/data-pump-enhancements-21chttps://oracle-base.com/articles/21c/data-pump-enhancements-21c 原文作者：Tim Hall @TOC目录 本文主要概述了 Oracle Database 21c 中的数据泵增强功能。 在可插拔数据库中
原文地址：https://oracle-base.com/articles/21c/data-pump-enhancements-21c 原文作者：Tim Hall
本文主要概述了 Oracle Database 21c 中的数据泵增强功能。
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
在可插拔数据库中创建一个 testuser1 用户：
1conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba23--drop user testuser1 cascade;4create user testuser1 identified by testuser1 quota unlimited on users;5grant connect, resource to testuser1;6grant select_catalog_role to testuser1;创建一个新的目录并授予测试用户访问权限：
1create or replace directory tmp_dir as '/tmp/';2grant read, write on directory tmp_dir to testuser1;在测试用户下创建以下表并插入数据：
1conn testuser1/testuser1@//localhost:1521/pdb123-- drop table t1 purge;45create table t1 (6 id number generated always as identity,7 json_data json,8 constraint ta_pk primary key (id)9);1011insert into t1 (json_data) values (json('{"fruit":"apple","quantity":10}'));12insert into t1 (json_data) values (json('{"fruit":"orange","quantity":20}'));13commit;Oracle 21C 的导出和导入实用程序已包括对新 JSON 数据类型的支持。
以下示例，通过 expdp 实用程序导出 T1 表，需要注意的是，T1 表需要包含使用新 JSON 数据类型定义的列。
1$ expdp testuser1/testuser1@//localhost:1521/pdb1 \2 tables=t1 \3 directory=tmp_dir \4 dumpfile=t1.dmp \5 logfile=expdp_t1.log \6 exclude=statistics78Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:41:15 20219Version 21.3.0.0.01011Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1213Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production14Starting "TESTUSER1"."SYS_EXPORT_TABLE_01": testuser1/********@//localhost:1521/pdb115 tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=expdp_t1.log exclude=statistics16Processing object type TABLE_EXPORT/TABLE/TABLE_DATA17Processing object type TABLE_EXPORT/TABLE/TABLE18Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN19Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT20. . exported "TESTUSER1"."T1" 6.070 KB 2 rows21Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded22******************************************************************************23Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:24 /tmp/t1.dmp25Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 08:41:45 2021 elapsed 0 00:00:282627$我们导入 DUMP 文件，将表名重新映射为 T1_COPY：
1$ impdp testuser1/testuser1@//localhost:1521/pdb1 \2 tables=t1 \3 directory=tmp_dir \4 dumpfile=t1.dmp \5 logfile=impdp_t1.log \6 remap_table=testuser1.t1:t1_copy78Import: Release 21.0.0.0.0 - Production on Sun Sep 5 08:46:32 20219Version 21.3.0.0.01011Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1213Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production14Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded15Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1 16 tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=impdp_t1.log remap_table=testuser1.t1:t1_copy17Processing object type TABLE_EXPORT/TABLE/TABLE18Processing object type TABLE_EXPORT/TABLE/TABLE_DATA19. . imported "TESTUSER1"."T1_COPY" 6.070 KB 2 rows20Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN21Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT22ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists2324Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 08:46:39 2021 elapsed 0 00:00:052526$计算校验需要花费时间，DUMP 文件越大，计算校验所需的工作量就越大。
Oracle 21C 数据库泵已添加 CHECKSUM 和 CHECKSUM_ALGORITHM 参数，以防止 DUMP 文件的数据被篡改。如果我们设置 CHECKSUM_ALGORITHM 参数，那么 CHECKSUM 参数默认为 yes。如果两者都没有设置，则CHECKSUM 参数默认为 no。CHECKSUM_ALGORITHM 参数可以设置为 CRC32、SHA256、SHA384 或 SHA512，默认为 SHA256。
在下面的示例中，我们启用 CHECKSUM, 并将 显式设置 CHECKSUM_ALGORITHM 为模式导出的默认值：
1$ expdp testuser1/testuser1@//localhost:1521/pdb1 \2 schemas=testuser1 \3 directory=tmp_dir \4 dumpfile=testuser1.dmp \5 logfile=expdp_testuser1.log \6 exclude=statistics \7 checksum=yes \8 checksum_algorithm=SHA256910Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:58:55 202111Version 21.3.0.0.01213Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1415Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production16Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01": testuser1/********@//localhost:1521/pdb117 schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log exclude=statistics checksum=yes checksum_algorithm=SHA25618Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA19Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA20Processing object type SCHEMA_EXPORT/TABLE/TABLE21Processing object type SCHEMA_EXPORT/TABLE/COMMENT22Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN23Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX24Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT25. . exported "TESTUSER1"."T1" 6.070 KB 2 rows26. . exported "TESTUSER1"."T1_COPY" 6.078 KB 2 rows27Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded28Generating checksums for dump file set29******************************************************************************30Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:31 /tmp/testuser1.dmp32Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 08:59:38 2021 elapsed 0 00:00:413334$我们可以使用 VERIFY_ONLY 参数验证转储文件的校验：
1$ impdp testuser1/testuser1@//localhost:1521/pdb1 \2 directory=tmp_dir \3 dumpfile=testuser1.dmp \4 verify_only=yes56Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:10:55 20217Version 21.3.0.0.089Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1011Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production12Verifying dump file checksums13Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded14dump file set is complete15verified checksum for dump file "/tmp/testuser1.dmp"16dump file set is consistent17Job "TESTUSER1"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 5 09:10:57 2021 elapsed 0 00:00:011819$在导入过程中，我们可以使用 VERIFY_CHECKSUM 参数来验证校验。如果验证失败，则不会进行导入。如果不使用 VERIFY_CHECKSUM 参数，即使校验不正确，导入也会继续。
1$ impdp testuser1/testuser1@//localhost:1521/pdb1 \2 tables=t1 \3 directory=tmp_dir \4 dumpfile=testuser1.dmp \5 logfile=impdp_t1_copy_again.log \6 remap_table=testuser1.t1:t1_copy_again \7 verify_checksum=yes89Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:16:24 202110Version 21.3.0.0.01112Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1314Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production15Verifying dump file checksums16Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded17Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb118 tables=t1 directory=tmp_dir dumpfile=testuser1.dmp logfile=impdp_t1_copy_again.log19 remap_table=testuser1.t1:t1_copy_again verify_checksum=yes20Processing object type SCHEMA_EXPORT/TABLE/TABLE21Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA22. . imported "TESTUSER1"."T1_COPY_AGAIN" 6.070 KB 2 rows23Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN24Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT25ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists2627Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 09:16:30 2021 elapsed 0 00:00:042829$在 Oracle 21C 中，INCLUDE 和 EXCLUDE 参数可以同时是一条命令的一部分。在以前的版本中 INCLUDE，EXCLUDE 参数是互斥的，只能存在一个。
以下示例在单个命令中同时使用 INCLUDE 和 EXCLUDE 参数：
1$ expdp testuser1/testuser1@//localhost:1521/pdb1 \2 schemas=testuser1 \3 directory=tmp_dir \4 dumpfile=testuser1.dmp \5 logfile=expdp_testuser1.log \6 include="table:\"in ('T1')\"" \7 exclude="table:\"in ('T1_COPY','T1_COPY_AGAIN')\"" \8 exclude=statistics910Export: Release 21.0.0.0.0 - Production on Sun Sep 5 10:54:03 202111Version 21.3.0.0.01213Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1415Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production16Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01": testuser1/********@//localhost:1521/pdb117 schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log18 include=table:"in ('T1')" exclude=table:"in ('T1_COPY','T1_COPY_AGAIN')" exclude=statistics19Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA20Processing object type SCHEMA_EXPORT/TABLE/TABLE21Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN22Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT23. . exported "TESTUSER1"."T1" 6.070 KB 2 rows24Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded25******************************************************************************26Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:27 /tmp/testuser1.dmp28Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 10:54:31 2021 elapsed 0 00:00:272930$在 Oracle 21C 中，我们可以选择使用 TRANSFORM 参数和 INDEX_COMPRESSION_CLAUSE 来进行索引压缩。
首先，创建一个带有一些索引的测试表：
1conn testuser1/testuser1@//localhost:1521/pdb123-- drop table t2 purge;45create table t2 as6 select level as id,7 'Description for ' || level as col1,8 case mod(level, 2)9 when 0 then 'one'10 else 'two'11 end as col2,12 trunc(dbms_random.value(0,10)) as col3,13 trunc(dbms_random.value(0,20)) as col414 from dual15 connect by level <= 10000;1617alter table t2 add constraint t2_pk primary key (id);18create index t2_col1_idx on t2(col1);19create index t2_col2_idx on t2(col2);20create index t2_col3_idx on t2(col3);21create index t2_col4_idx on t2(col4);检查表和索引是否开启压缩：
1select compression2from user_tables3where table_name = 'T2';45COMPRESS6--------7DISABLED89SQL>101112column index_name format a121314select index_name,15 compression16from user_indexes17where table_name = 'T2'18order by 1;1920INDEX_NAME COMPRESSION21------------ -------------22T2_COL1_IDX DISABLED23T2_COL2_IDX DISABLED24T2_COL3_IDX DISABLED25T2_COL4_IDX DISABLED26T2_PK DISABLED2728SQL>导出表：
1$ expdp testuser1/testuser1@//localhost:1521/pdb1 \2 tables=t2 \3 directory=tmp_dir \4 dumpfile=t2.dmp \5 logfile=expdp_t2.log \6 exclude=statistics78Export: Release 21.0.0.0.0 - Production on Sun Sep 5 11:57:18 20219Version 21.3.0.0.01011Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1213Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production14Starting "TESTUSER1"."SYS_EXPORT_TABLE_01": testuser1/********@//localhost:1521/pdb115 tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=expdp_t2.log exclude=statistics16Processing object type TABLE_EXPORT/TABLE/TABLE_DATA17Processing object type TABLE_EXPORT/TABLE/TABLE18Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX19Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT20. . exported "TESTUSER1"."T2" 384.8 KB 10000 rows21Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded22******************************************************************************23Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:24 /tmp/t2.dmp25Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 11:57:35 2021 elapsed 0 00:00:142627$删除源表，以便我们重新导入它：
1conn testuser1/testuser1@//localhost:1521/pdb123drop table t2 purge;从 DUMP 文件导入表，通过 TRANSFORM 参数组合 TABLE_COMPRESSION_CLAUSE 来压缩使用表，INDEX_COMPRESSION_CLAUSE 来压缩索引：
1$ impdp testuser1/testuser1@//localhost:1521/pdb1 \2 tables=t2 \3 directory=tmp_dir \4 dumpfile=t2.dmp \5 logfile=impdp_t2.log \6 transform=table_compression_clause:\"compress basic\" \7 transform=index_compression_clause:\"compress advanced low\"89Import: Release 21.0.0.0.0 - Production on Sun Sep 5 12:02:22 202110Version 21.3.0.0.01112Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.1314Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production15Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded16Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb117 tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=impdp_t2.log18 transform=table_compression_clause:"compress basic" transform=index_compression_clause:"compress advanced low"19Processing object type TABLE_EXPORT/TABLE/TABLE20Processing object type TABLE_EXPORT/TABLE/TABLE_DATA21. . imported "TESTUSER1"."T2" 384.8 KB 10000 rows22Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX23Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT24Job "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully completed at Sun Sep 5 12:02:29 2021 elapsed 0 00:00:052526$检查表和索引是否开启压缩：
1conn testuser1/testuser1@//localhost:1521/pdb123select compression4from user_tables5where table_name = 'T2';67COMPRESS8--------9ENABLED1011SQL>121314column index_name format a121516select index_name,17 compression18from user_indexes19where table_name = 'T2'20order by 1;2122INDEX_NAME COMPRESSION23------------ -------------24T2_COL1_IDX ADVANCED LOW25T2_COL2_IDX ADVANCED LOW26T2_COL3_IDX ADVANCED LOW27T2_COL4_IDX ADVANCED LOW28T2_PK DISABLED2930SQL>我们可以看到表和索引现在都被压缩了。
在 Oracle 21c 中，可传输表空间导出 (expdp) 和导入 (impdp) 可以使用 PARALLEL 参数来并行化操作。
在 Oracle 21c 中，数据泵可以在故障点或故障点附近恢复失败的可传输表空间作业。在以前的版本中，可传输表空间作业无法恢复。
我们可以使用本地 Oracle 21.3 安装将数据从自治数据库导出到使用该expdp实用程序的对象存储。您可以在本文中阅读有关此功能的信息。
Data Pump 支持将云对象存储作为转储文件位置，用于从 Oracle 21c 开始的本地安装。