原文地址：
原文作者：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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。