原文地址：https://oracle-base.com/articles/21c/data-pump-enhancements-21c
原文作者：Tim Hall
目錄
設置
在您的可插入數據庫中創建一個測試用戶。
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant select_catalog_role to testuser1;
Create a new directory object and grant access to the test user.
創建一個新的目錄對象并授予測試用戶訪問權限。
create or replace directory tmp_dir as '/tmp/';
grant read, write on directory tmp_dir to testuser1;
在您的測試架構中創建并填充下表。
conn testuser1/testuser1@//localhost:1521/pdb1
-- drop table t1 purge;
create table t1 (
id number generated always as identity,
json_data json,
constraint ta_pk primary key (id)
);
insert into t1 (json_data) values (json('{"fruit":"apple","quantity":10}'));
insert into t1 (json_data) values (json('{"fruit":"orange","quantity":20}'));
commit;
JSON 數據類型支持
導出和導入實用程序包括對新 JSON 數據類型的支持。
以下示例T1使用expdp實用程序導出表。請記住，該T1表包含一個使用新 JSON 數據類型定義的列。
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
tables=t1 \
directory=tmp_dir \
dumpfile=t1.dmp \
logfile=expdp_t1.log \
exclude=statistics
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:41:15 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1
tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=expdp_t1.log exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1" 6.070 KB 2 rows
Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:
/tmp/t1.dmp
Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 08:41:45 2021 elapsed 0 00:00:28
$
我們導入轉儲文件，將表名重新映射到T1_COPY.
$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
tables=t1 \
directory=tmp_dir \
dumpfile=t1.dmp \
logfile=impdp_t1.log \
remap_table=testuser1.t1:t1_copy
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 08:46:32 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1
tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=impdp_t1.log remap_table=testuser1.t1:t1_copy
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T1_COPY" 6.070 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists
Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 08:46:39 2021 elapsed 0 00:00:05
$
CHECKSUM、CHECKSUM_ALGORITHM、VERIFY_ONLY 和 VERIFY_CHECKSUM 參數
計算校驗和需要時間。轉儲文件越大，計算校驗和所需的工作就越多。
在CHECKSUM和CHECKSUM_ALGORITHM已經添加的參數，以防止轉儲文件的數據，當他們在其他磁盤上的篡改。如果我們設置了CHECKSUM_ALGORITHM參數，那么CHECKSUM參數默認為yes。如果兩者都未設置，則 code>CHECKSUM 參數默認為 no。該CHECKSUM_ALGORITHM參數可以設置為CRC32、SHA256、SHA384或SHA512，默認為SHA256。
在以下示例中，我們啟用CHECKSUM，并將 顯式設置CHECKSUM_ALGORITHM為模式導出的默認值。
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
schemas=testuser1 \
directory=tmp_dir \
dumpfile=testuser1.dmp \
logfile=expdp_testuser1.log \
exclude=statistics \
checksum=yes \
checksum_algorithm=SHA256
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:58:55 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01": testuser1/********@//localhost:1521/pdb1
schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log exclude=statistics checksum=yes checksum_algorithm=SHA256
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1" 6.070 KB 2 rows
. . exported "TESTUSER1"."T1_COPY" 6.078 KB 2 rows
Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:
/tmp/testuser1.dmp
Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 08:59:38 2021 elapsed 0 00:00:41
$
我們可以使用VERIFY_ONLY參數驗證轉儲文件的校驗和。
$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
directory=tmp_dir \
dumpfile=testuser1.dmp \
verify_only=yes
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:10:55 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/tmp/testuser1.dmp"
dump file set is consistent
Job "TESTUSER1"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 5 09:10:57 2021 elapsed 0 00:00:01
$
我們VERIFY_CHECKSUM在導入過程中使用該參數來驗證校驗和。如果驗證失敗，則不會進行導入。如果我們不使用該VERIFY_CHECKSUM參數，即使校驗和不正確，導入也會繼續。
$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
tables=t1 \
directory=tmp_dir \
dumpfile=testuser1.dmp \
logfile=impdp_t1_copy_again.log \
remap_table=testuser1.t1:t1_copy_again \
verify_checksum=yes
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:16:24 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1
tables=t1 directory=tmp_dir dumpfile=testuser1.dmp logfile=impdp_t1_copy_again.log
remap_table=testuser1.t1:t1_copy_again verify_checksum=yes
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T1_COPY_AGAIN" 6.070 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists
Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 09:16:30 2021 elapsed 0 00:00:04
$
INCLUDE 和 EXCLUDE 在同一操作中
在Oracle數據庫21c中，INCLUDE并且EXCLUDE參數可以是相同的命令的一部分。在以前的版本中INCLUDE，EXCLUDE參數是互斥的。
以下示例在單個命令中組合了INCLUDE和EXCLUDE參數。我們必須為命令行轉義一些引號。
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
schemas=testuser1 \
directory=tmp_dir \
dumpfile=testuser1.dmp \
logfile=expdp_testuser1.log \
include="table:\"in ('T1')\"" \
exclude="table:\"in ('T1_COPY','T1_COPY_AGAIN')\"" \
exclude=statistics
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 10:54:03 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01": testuser1/********@//localhost:1521/pdb1
schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log
include=table:"in ('T1')" exclude=table:"in ('T1_COPY','T1_COPY_AGAIN')" exclude=statistics
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1" 6.070 KB 2 rows
Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:
/tmp/testuser1.dmp
Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 10:54:31 2021 elapsed 0 00:00:27
$
索引壓縮
在 Oracle 數據庫 21c 中，我們可以選擇在導入時使用TRANSFORM參數和INDEX_COMPRESSION_CLAUSE.
創建一個帶有一些索引的測試表。
conn testuser1/testuser1@//localhost:1521/pdb1
-- drop table t2 purge;
create table t2 as
select level as id,
'Description for ' || level as col1,
case mod(level, 2)
when 0 then 'one'
else 'two'
end as col2,
trunc(dbms_random.value(0,10)) as col3,
trunc(dbms_random.value(0,20)) as col4
from dual
connect by level <= 10000;
alter table t2 add constraint t2_pk primary key (id);
create index t2_col1_idx on t2(col1);
create index t2_col2_idx on t2(col2);
create index t2_col3_idx on t2(col3);
create index t2_col4_idx on t2(col4);
檢查表和索引的壓縮。
select compression
from user_tables
where table_name = 'T2';
COMPRESS
--------
DISABLED
SQL>
column index_name format a12
select index_name,
compression
from user_indexes
where table_name = 'T2'
order by 1;
INDEX_NAME COMPRESSION
------------ -------------
T2_COL1_IDX DISABLED
T2_COL2_IDX DISABLED
T2_COL3_IDX DISABLED
T2_COL4_IDX DISABLED
T2_PK DISABLED
SQL>
導出表。
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
tables=t2 \
directory=tmp_dir \
dumpfile=t2.dmp \
logfile=expdp_t2.log \
exclude=statistics
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 11:57:18 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1
tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=expdp_t2.log exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T2" 384.8 KB 10000 rows
Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:
/tmp/t2.dmp
Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 11:57:35 2021 elapsed 0 00:00:14
$
刪除表，以便我們可以重新導入它。
conn testuser1/testuser1@//localhost:1521/pdb1
drop table t2 purge;
從轉儲文件中導入表，使用TRANSFORM參數來壓縮表TABLE_COMPRESSION_CLAUSE并使用INDEX_COMPRESSION_CLAUSE.
$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
tables=t2 \
directory=tmp_dir \
dumpfile=t2.dmp \
logfile=impdp_t2.log \
transform=table_compression_clause:\"compress basic\" \
transform=index_compression_clause:\"compress advanced low\"
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 12:02:22 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01": testuser1/********@//localhost:1521/pdb1
tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=impdp_t2.log
transform=table_compression_clause:"compress basic" transform=index_compression_clause:"compress advanced low"
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T2" 384.8 KB 10000 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully completed at Sun Sep 5 12:02:29 2021 elapsed 0 00:00:05
$
檢查表和索引的壓縮。
conn testuser1/testuser1@//localhost:1521/pdb1
select compression
from user_tables
where table_name = 'T2';
COMPRESS
--------
ENABLED
SQL>
column index_name format a12
select index_name,
compression
from user_indexes
where table_name = 'T2'
order by 1;
INDEX_NAME COMPRESSION
------------ -------------
T2_COL1_IDX ADVANCED LOW
T2_COL2_IDX ADVANCED LOW
T2_COL3_IDX ADVANCED LOW
T2_COL4_IDX ADVANCED LOW
T2_PK DISABLED
SQL>
我們可以看到表和索引現在都被壓縮了。
您可以在此處閱讀該TRANSFORM參數的完整說明。有關索引壓縮的信息，請參閱此處有關CREATE INDEX的聲明。
可傳輸表空間增強
在 Oracle 21c 中，可傳輸表空間導出 ( expdp) 和導入 ( impdp) 現在可以使用PARALLEL參數來并行化操作。
在 Oracle 21c 中，數據泵可以在故障點或故障點附近恢復失敗的可傳輸表空間作業。在以前的版本中，無法恢復可傳輸表空間作業。
從 Oracle 自治數據庫導出
我們可以使用本地 Oracle 21.3 安裝將數據從自治數據庫導出到使用該expdp實用程序的對象存儲。您可以在本文中閱讀有關此功能的信息。
從 Cloud Object Store 導出和導入
此功能在 Oracle 21.3 中似乎不起作用。我有一個關于這個問題的 SR 與 Oracle 支持。一旦我看到它工作，就會有一篇關于這個功能的單獨文章。
最后修改時間：2022-01-05 16:08:29
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
