实验环境准备
利用OCI上的Oracle数据库image建立3套单实例数据库环境，SYS口令为Ora_DB4U，普通用户口令为Welcome1：
| 数据库环境/主机名 | 版本 | 公网IP | 私网IP | DB_NAME | CDB | PDB |
|---|---|---|---|---|---|---|
| 源数据库1/db122-noncdb | 12.2 | 138.3.221.188 | 10.0.0.15 | NONCDB12 | N | N/A |
| 源数据库2/db122-cdb | 12.2 | 158.101.152.60 | 10.0.0.107 | CDB12 | Y | ORCLPDB1 |
| 目标数据库/db19-cdb | 19c | 158.101.67.212 | 10.0.0.203 | DB19 | Y | ORCLPDB1 |
源数据库是指需要迁移的数据库，目标数据库指需要迁往的数据库。
源数据库上均安装了Oracle示例Schema。安装方法请参见如何使用github安装Oracle 数据库12c Sample Schema （示例Schema）。
源数据库中对象的情况如下：
system@ORCLPDB1> select owner, count(*) from dba_objects where owner in
(select username from all_users where ORACLE_MAINTAINED = 'N') group by owner order by owner;
OWNER COUNT(*)
-------------------- ----------
BI 8
HR 34
IX 58
OE 142
PM 22
SH 310
6 rows selected.
3个数据库服务器均开放1521端口：
firewall-cmd --add-port=1521/tcp --permanent
firewall-cmd --reload
# firewall-cmd --list-all
public
target: default
icmp-block-inversion: no
interfaces:
sources:
services: dhcpv6-client ssh
ports: 1521/tcp
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
3个数据库服务器均通过/etc/hosts设置了主机名解析：
10.0.0.15 db122-noncdb
10.0.0.107 db122-cdb
10.0.0.203 db19-cdb
3个数据库服务器均在oracle用户根目录下建立了dumpdir目录：
$ id -un
oracle
$ cd ~
$ mkdir dumpdir
实验1：Data Pump实现Schema迁移
源数据库：db122-cdb上的orclpdb1中的所有用户Schema
目标数据库：db19-cdb上的orclpdb2
源数据库服务器上的操作
准备data pump参数文件export.par：
[oracle@db122-cdb dumpdir]$ cat export.par
DIRECTORY=my_data_pump_dir
DUMPFILE=dumpfile%U.dmp
LOGFILE=logfile.log
SCHEMAS=HR,OE,PM,IX,SH,BI
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
FLASHBACK_TIME=SYSTIMESTAMP
PARALLEL=4
FILESIZE=5G
定义目录：
sys@CDB12> alter session set container=orclpdb1;
sys@CDB12> create directory my_data_pump_dir as '/home/oracle/dumpdir';
执行导出：
[oracle@db122-cdb dumpdir]$ expdp \"sys/Ora_DB4U@orclpdb1 as sysdba\" parfile=export.par
Export: Release 12.2.0.1.0 - Production on Thu Nov 25 00:38:37 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
25-NOV-21 00:38:40.791: Starting "SYS"."SYS_EXPORT_SCHEMA_01": "sys/********@orclpdb1 AS SYSDBA" parfile=export.par
25-NOV-21 00:38:40.971: W-1 Startup took 2 seconds
25-NOV-21 00:38:42.295: W-4 Startup took 3 seconds
25-NOV-21 00:38:42.454: W-3 Startup took 3 seconds
25-NOV-21 00:38:42.577: W-2 Startup took 3 seconds
25-NOV-21 00:38:47.049: W-3 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
25-NOV-21 00:38:47.138: W-4 Processing object type SCHEMA_EXPORT/USER
25-NOV-21 00:38:47.241: W-2 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
25-NOV-21 00:38:47.270: W-3 Processing object type SCHEMA_EXPORT/ROLE_GRANT
25-NOV-21 00:38:47.288: W-4 Completed 6 USER objects in 1 seconds
25-NOV-21 00:38:47.296: W-2 Completed 51 SYSTEM_GRANT objects in 0 seconds
25-NOV-21 00:38:47.359: W-3 Completed 13 ROLE_GRANT objects in 0 seconds
25-NOV-21 00:38:47.423: W-4 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
25-NOV-21 00:38:47.430: W-4 Completed 6 DEFAULT_ROLE objects in 0 seconds
25-NOV-21 00:38:47.582: W-3 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
25-NOV-21 00:38:47.589: W-3 Completed 6 TABLESPACE_QUOTA objects in 0 seconds
25-NOV-21 00:38:48.001: W-2 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
25-NOV-21 00:38:48.002: W-2 Completed 7 PROCACT_SCHEMA objects in 0 seconds
25-NOV-21 00:38:48.484: W-2 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
25-NOV-21 00:38:48.504: W-2 Completed 14 SYNONYM objects in 0 seconds
25-NOV-21 00:38:48.935: W-3 Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
25-NOV-21 00:38:48.954: W-3 Completed 4 INC_TYPE objects in 0 seconds
25-NOV-21 00:38:49.347: W-2 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
25-NOV-21 00:38:49.419: W-2 Completed 32 TYPE objects in 1 seconds
25-NOV-21 00:38:49.574: W-3 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
25-NOV-21 00:38:49.593: W-3 Completed 6 SEQUENCE objects in 0 seconds
25-NOV-21 00:38:51.023: W-1 Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:38:51.053: W-1 Completed 10 OBJECT_GRANT objects in 3 seconds
25-NOV-21 00:38:53.734: W-2 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
25-NOV-21 00:38:53.939: W-2 Completed 166 COMMENT objects in 0 seconds
25-NOV-21 00:38:54.567: W-1 Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA
25-NOV-21 00:38:54.583: W-1 Completed 1 XMLSCHEMA objects in 3 seconds
25-NOV-21 00:38:55.182: W-3 Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:38:55.273: W-3 Completed 35 OBJECT_GRANT objects in 2 seconds
25-NOV-21 00:38:55.798: W-3 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
25-NOV-21 00:38:55.804: W-3 Completed 1 FUNCTION objects in 0 seconds
25-NOV-21 00:38:56.421: W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
25-NOV-21 00:38:56.432: W-1 Completed 2 PROCEDURE objects in 0 seconds
25-NOV-21 00:38:57.129: W-4 Processing object type SCHEMA_EXPORT/TABLE/TABLE
25-NOV-21 00:38:57.491: W-1 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
25-NOV-21 00:38:57.504: W-1 Completed 1 ALTER_FUNCTION objects in 0 seconds
25-NOV-21 00:38:57.831: W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
25-NOV-21 00:38:57.849: W-1 Completed 2 ALTER_PROCEDURE objects in 0 seconds
25-NOV-21 00:39:00.069: W-3 Processing object type SCHEMA_EXPORT/VIEW/COMMENT
25-NOV-21 00:39:00.088: W-3 Completed 4 COMMENT objects in 1 seconds
25-NOV-21 00:39:01.543: W-1 Processing object type SCHEMA_EXPORT/VIEW/VIEW
25-NOV-21 00:39:01.566: W-1 Completed 21 VIEW objects in 3 seconds
25-NOV-21 00:39:01.831: W-2 Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:39:01.848: W-2 Completed 5 OBJECT_GRANT objects in 2 seconds
25-NOV-21 00:39:01.855: W-3 Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
25-NOV-21 00:39:01.865: W-3 Completed 3 TYPE_BODY objects in 0 seconds
25-NOV-21 00:39:02.114: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
25-NOV-21 00:39:02.133: W-2 Completed 1 INDEX objects in 0 seconds
25-NOV-21 00:39:03.963: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
25-NOV-21 00:39:04.334: W-1 Completed 43 INDEX objects in 3 seconds
25-NOV-21 00:39:04.924: W-3 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
25-NOV-21 00:39:04.925: W-3 Completed 37 CONSTRAINT objects in 2 seconds
25-NOV-21 00:39:06.212: W-3 Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
25-NOV-21 00:39:06.535: W-2 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
25-NOV-21 00:39:06.578: W-2 Completed 30 REF_CONSTRAINT objects in 1 seconds
25-NOV-21 00:39:06.588: W-3 Completed 15 INDEX objects in 0 seconds
25-NOV-21 00:39:06.866: W-2 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
25-NOV-21 00:39:06.888: W-2 Completed 3 TRIGGER objects in 0 seconds
25-NOV-21 00:39:07.131: W-3 Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
25-NOV-21 00:39:07.160: W-3 Completed 2 TRIGGER objects in 1 seconds
25-NOV-21 00:39:08.333: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
25-NOV-21 00:39:08.347: W-1 Completed 1 INDEX objects in 0 seconds
25-NOV-21 00:39:08.587: W-3 Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
25-NOV-21 00:39:08.602: W-3 Completed 2 MATERIALIZED_VIEW objects in 0 seconds
25-NOV-21 00:39:09.147: W-3 Processing object type SCHEMA_EXPORT/DIMENSION
25-NOV-21 00:39:09.155: W-3 Completed 5 DIMENSION objects in 0 seconds
25-NOV-21 00:39:11.208: W-2 Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
25-NOV-21 00:39:11.222: W-2 Completed 10 PROCDEPOBJ objects in 1 seconds
25-NOV-21 00:39:11.586: W-3 Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
25-NOV-21 00:39:11.607: W-3 Completed 15 PROCACT_INSTANCE objects in 1 seconds
25-NOV-21 00:39:11.937: W-2 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
25-NOV-21 00:39:11.955: W-2 Completed 6 PROCOBJ objects in 0 seconds
25-NOV-21 00:39:12.548: W-3 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
25-NOV-21 00:39:12.816: W-2 . . exported "SH"."CUSTOMERS" 10.27 MB 55500 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.024: W-2 . . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows in 1 seconds using direct_path
25-NOV-21 00:39:13.036: W-3 Completed 1 PROCACT_SCHEMA objects in 1 seconds
25-NOV-21 00:39:13.189: W-2 . . exported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.309: W-2 . . exported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.369: W-3 . . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.452: W-2 . . exported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.504: W-3 . . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.594: W-2 . . exported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.618: W-3 . . exported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.778: W-3 . . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.816: W-2 . . exported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.922: W-3 . . exported "SH"."SALES":"SALES_Q3_2000" 1.910 MB 58950 rows in 0 seconds using direct_path
25-NOV-21 00:39:13.964: W-2 . . exported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.114: W-2 . . exported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows in 1 seconds using direct_path
25-NOV-21 00:39:14.127: W-3 . . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.6 KB 4500 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.184: W-3 . . exported "SH"."TIMES" 381.7 KB 1826 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.229: W-3 . . exported "SH"."FWEEK_PSCAT_SALES_MV" 419.9 KB 11266 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.240: W-2 . . exported "SH"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.279: W-3 . . exported "SH"."COSTS":"COSTS_Q4_2001" 278.5 KB 9011 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.321: W-3 . . exported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.348: W-2 . . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.369: W-3 . . exported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.418: W-3 . . exported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.456: W-2 . . exported "SH"."SALES":"SALES_Q1_1998" 1.413 MB 43687 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.477: W-3 . . exported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.525: W-3 . . exported "SH"."COSTS":"COSTS_Q4_2000" 160.4 KB 5088 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.568: W-2 . . exported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.595: W-3 . . exported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.689: W-3 . . exported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.708: W-3 . . exported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.759: W-3 . . exported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.848: W-3 . . exported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.891: W-3 . . exported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.943: W-3 . . exported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows in 0 seconds using direct_path
25-NOV-21 00:39:14.990: W-3 . . exported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows in 0 seconds using direct_path
25-NOV-21 00:39:15.036: W-3 . . exported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows in 1 seconds using direct_path
25-NOV-21 00:39:15.265: W-3 . . exported "OE"."PRODUCT_INFORMATION" 73.05 KB 288 rows in 0 seconds using direct_path
25-NOV-21 00:39:15.424: W-3 . . exported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows in 0 seconds using direct_path
25-NOV-21 00:39:15.557: W-3 . . exported "OE"."CUSTOMERS" 81.17 KB 319 rows in 0 seconds using direct_path
25-NOV-21 00:39:15.730: W-3 . . exported "SH"."PROMOTIONS" 59.17 KB 503 rows in 0 seconds using direct_path
25-NOV-21 00:39:15.789: W-3 . . exported "SH"."PRODUCTS" 26.71 KB 72 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.746: W-1 . . exported "OE"."ORDER_ITEMS" 21.01 KB 665 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.781: W-1 . . exported "OE"."INVENTORIES" 21.76 KB 1112 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.834: W-1 . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.886: W-1 . . exported "PM"."TEXTDOCS_NESTEDTAB" 87.85 KB 12 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.928: W-1 . . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.57 KB 288 rows in 0 seconds using direct_path
25-NOV-21 00:39:25.980: W-1 . . exported "OE"."ORDERS" 12.59 KB 105 rows in 0 seconds using direct_path
25-NOV-21 00:39:26.024: W-1 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_S" 11.57 KB 1 rows in 1 seconds using direct_path
25-NOV-21 00:39:26.074: W-1 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_S" 11.30 KB 4 rows in 0 seconds using direct_path
25-NOV-21 00:39:26.121: W-1 . . exported "SH"."COUNTRIES" 10.46 KB 23 rows in 0 seconds using direct_path
25-NOV-21 00:39:26.980: W-1 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_H" 9.328 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:27.118: W-4 Completed 45 TABLE objects in 36 seconds
25-NOV-21 00:39:27.247: W-4 . . exported "OE"."CATEGORIES_TAB" 14.43 KB 22 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.322: W-4 . . exported "HR"."LOCATIONS" 8.437 KB 23 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.372: W-3 . . exported "PM"."PRINT_MEDIA" 190.6 KB 4 rows in 12 seconds using external_table
25-NOV-21 00:39:27.376: W-4 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_L" 8.039 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.519: W-3 . . exported "SH"."CHANNELS" 7.414 KB 5 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.571: W-3 . . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.617: W-3 . . exported "HR"."JOBS" 7.109 KB 19 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.670: W-3 . . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.711: W-1 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_I" 8.890 KB 0 rows in 1 seconds using external_table
25-NOV-21 00:39:27.726: W-3 . . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.656 KB 21 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.930: W-1 . . exported "SH"."CAL_MONTH_SALES_MV" 6.382 KB 48 rows in 0 seconds using direct_path
25-NOV-21 00:39:27.943: W-2 . . exported "OE"."PURCHASEORDER" 247.7 KB 132 rows in 13 seconds using external_table
25-NOV-21 00:39:28.001: W-1 . . exported "HR"."REGIONS" 5.546 KB 4 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.048: W-1 . . exported "OE"."PROMOTIONS" 5.570 KB 2 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.164: W-1 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_L" 8.039 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.169: W-4 . . exported "OE"."WAREHOUSES" 12.76 KB 9 rows in 1 seconds using external_table
25-NOV-21 00:39:28.310: W-4 . . exported "SH"."COSTS":"COSTS_1995" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.339: W-4 . . exported "SH"."COSTS":"COSTS_1996" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.387: W-4 . . exported "SH"."COSTS":"COSTS_H1_1997" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.433: W-4 . . exported "SH"."COSTS":"COSTS_H2_1997" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.476: W-3 . . exported "HR"."COUNTRIES" 6.367 KB 25 rows in 1 seconds using external_table
25-NOV-21 00:39:28.491: W-4 . . exported "SH"."COSTS":"COSTS_Q1_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.550: W-4 . . exported "SH"."COSTS":"COSTS_Q1_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.599: W-4 . . exported "SH"."COSTS":"COSTS_Q2_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.629: W-4 . . exported "SH"."COSTS":"COSTS_Q2_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.654: W-2 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_G" 13.61 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:28.672: W-4 . . exported "SH"."COSTS":"COSTS_Q3_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.699: W-4 . . exported "SH"."COSTS":"COSTS_Q3_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.726: W-4 . . exported "SH"."COSTS":"COSTS_Q4_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.761: W-4 . . exported "SH"."COSTS":"COSTS_Q4_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.800: W-4 . . exported "SH"."SALES":"SALES_1995" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.826: W-4 . . exported "SH"."SALES":"SALES_1996" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.846: W-1 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_T" 6.343 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:28.860: W-4 . . exported "SH"."SALES":"SALES_H1_1997" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.889: W-4 . . exported "SH"."SALES":"SALES_H2_1997" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.915: W-4 . . exported "SH"."SALES":"SALES_Q1_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.940: W-4 . . exported "SH"."SALES":"SALES_Q1_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.969: W-4 . . exported "SH"."SALES":"SALES_Q2_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:28.994: W-4 . . exported "SH"."SALES":"SALES_Q2_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:29.023: W-4 . . exported "SH"."SALES":"SALES_Q3_2002" 7.664 KB 0 rows in 1 seconds using direct_path
25-NOV-21 00:39:29.052: W-4 . . exported "SH"."SALES":"SALES_Q3_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:29.078: W-4 . . exported "SH"."SALES":"SALES_Q4_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:29.105: W-4 . . exported "SH"."SALES":"SALES_Q4_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:39:29.218: W-2 . . exported "IX"."AQ$_ORDERS_QUEUETABLE_T" 6.335 KB 0 rows in 1 seconds using external_table
25-NOV-21 00:39:29.379: W-1 . . exported "IX"."ORDERS_QUEUETABLE" 21.33 KB 0 rows in 1 seconds using external_table
25-NOV-21 00:39:29.627: W-2 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_C" 5.898 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:29.803: W-1 . . exported "IX"."STREAMS_QUEUE_TABLE" 17.69 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:30.027: W-2 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_G" 13.61 KB 0 rows in 1 seconds using external_table
25-NOV-21 00:39:30.392: W-2 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_H" 9.328 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:30.757: W-2 . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_I" 9.296 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:39:31.233: W-1 Completed 101 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 18 seconds
25-NOV-21 00:39:32.449: W-1 Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
25-NOV-21 00:39:32.493: ******************************************************************************
25-NOV-21 00:39:32.494: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
25-NOV-21 00:39:32.496: /home/oracle/dumpdir/dumpfile01.dmp
25-NOV-21 00:39:32.496: /home/oracle/dumpdir/dumpfile02.dmp
25-NOV-21 00:39:32.497: /home/oracle/dumpdir/dumpfile03.dmp
25-NOV-21 00:39:32.497: /home/oracle/dumpdir/dumpfile04.dmp
25-NOV-21 00:39:32.526: Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Nov 25 00:39:32 2021 elapsed 0 00:00:55
以上expdp命令若用system用户执行，会报如下错误：
25-NOV-21 00:37:23.612: ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER"
将导出文件传输到目标数据库服务器：
[oracle@db122-cdb dumpdir]$ scp dumpfile*.dmp db19-cdb:~/dumpdir
目标数据库服务器上的操作
使用脚本CreateTargetCDB.sql建立空的PDB：orclpdb2（因为已经有orclpdb1了）：
-- CreateTargetCDB.sql
alter pluggable database orclpdb2 close;
drop pluggable database orclpdb2 including datafiles;
create pluggable database orclpdb2 admin user pdbadmin identified by Welcome1
default tablespace users datafile '/u01/app/oracle/oradata/DB19/orclpdb2/user01.dbf' size 1m autoextend on next 1m
FILE_NAME_CONVERT = ('pdbseed', 'orclpdb2');
alter pluggable database orclpdb2 open;
alter pluggable database orclpdb2 save state;
alter session set container=orclpdb2;
create directory my_data_pump_dir as '/home/oracle/dumpdir';
import参数文件为：
[oracle@db19-cdb dumpdir]$ cat import.par
DIRECTORY=my_data_pump_dir
DUMPFILE=dumpfile%U.dmp
LOGFILE=logfile.log
SCHEMAS=HR,OE,PM,IX,SH,BI
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
REMAP_TABLESPACE=%:USERS
导入：
[oracle@db19-cdb dumpdir]$ impdp \"sys/Ora_DB4U@orclpdb2 as sysdba\" parfile=import.par
Import: Release 19.0.0.0.0 - Production on Thu Nov 25 00:48:25 2021
Version 19.12.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
25-NOV-21 00:48:30.180: W-1 Startup took 1 seconds
25-NOV-21 00:48:32.317: W-1 Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
25-NOV-21 00:48:32.729: Starting "SYS"."SYS_IMPORT_SCHEMA_01": "sys/********@orclpdb2 AS SYSDBA" parfile=import.par
25-NOV-21 00:48:32.788: W-1 Processing object type SCHEMA_EXPORT/USER
25-NOV-21 00:48:33.195: W-1 Completed 6 USER objects in 1 seconds
25-NOV-21 00:48:33.195: W-1 Completed by worker 1 6 USER objects in 1 seconds
25-NOV-21 00:48:33.198: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
25-NOV-21 00:48:33.485: W-1 Completed 51 SYSTEM_GRANT objects in 0 seconds
25-NOV-21 00:48:33.485: W-1 Completed by worker 1 51 SYSTEM_GRANT objects in 0 seconds
25-NOV-21 00:48:33.488: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
25-NOV-21 00:48:33.732: W-1 Completed 13 ROLE_GRANT objects in 0 seconds
25-NOV-21 00:48:33.732: W-1 Completed by worker 1 13 ROLE_GRANT objects in 0 seconds
25-NOV-21 00:48:33.735: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
25-NOV-21 00:48:33.972: W-1 Completed 6 DEFAULT_ROLE objects in 0 seconds
25-NOV-21 00:48:33.972: W-1 Completed by worker 1 6 DEFAULT_ROLE objects in 0 seconds
25-NOV-21 00:48:33.974: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
25-NOV-21 00:48:34.257: W-1 Completed 6 TABLESPACE_QUOTA objects in 0 seconds
25-NOV-21 00:48:34.257: W-1 Completed by worker 1 6 TABLESPACE_QUOTA objects in 0 seconds
25-NOV-21 00:48:34.259: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
25-NOV-21 00:48:34.815: W-1 Completed 7 PROCACT_SCHEMA objects in 0 seconds
25-NOV-21 00:48:34.815: W-1 Completed by worker 1 7 PROCACT_SCHEMA objects in 0 seconds
25-NOV-21 00:48:34.817: W-1 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
25-NOV-21 00:48:35.085: W-1 Completed 14 SYNONYM objects in 0 seconds
25-NOV-21 00:48:35.085: W-1 Completed by worker 1 14 SYNONYM objects in 0 seconds
25-NOV-21 00:48:35.088: W-1 Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
25-NOV-21 00:48:35.447: W-1 Completed 4 INC_TYPE objects in 0 seconds
25-NOV-21 00:48:35.447: W-1 Completed by worker 1 4 INC_TYPE objects in 0 seconds
25-NOV-21 00:48:35.449: W-1 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
25-NOV-21 00:48:36.527: W-1 Completed 32 TYPE objects in 1 seconds
25-NOV-21 00:48:36.527: W-1 Completed by worker 1 32 TYPE objects in 1 seconds
25-NOV-21 00:48:36.530: W-1 Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:48:36.786: W-1 Completed 10 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:48:36.786: W-1 Completed by worker 1 10 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:48:36.789: W-1 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
25-NOV-21 00:48:37.029: W-1 Completed 6 SEQUENCE objects in 0 seconds
25-NOV-21 00:48:37.029: W-1 Completed by worker 1 6 SEQUENCE objects in 0 seconds
25-NOV-21 00:48:37.032: W-1 Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA
25-NOV-21 00:48:38.679: W-1 Completed 1 XMLSCHEMA objects in 1 seconds
25-NOV-21 00:48:38.679: W-1 Completed by worker 1 1 XMLSCHEMA objects in 1 seconds
25-NOV-21 00:48:38.682: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
25-NOV-21 00:48:39.352: W-4 Startup took 1 seconds
25-NOV-21 00:48:39.423: W-3 Startup took 1 seconds
25-NOV-21 00:48:39.497: W-2 Startup took 1 seconds
25-NOV-21 00:48:47.504: ORA-39083: Object type TABLE:"SH"."SALES_TRANSACTIONS_EXT" failed to create with error:
ORA-06564: object DATA_FILE_DIR does not exist
Failing sql is:
CREATE TABLE "SH"."SALES_TRANSACTIONS_EXT" ("PROD_ID" NUMBER, "CUST_ID" NUMBER, "TIME_ID" DATE, "CHANNEL_ID" NUMBER, "PROMO_ID" NUMBER, "QUANTITY_SOLD" NUMBER, "AMOUNT_SOLD" NUMBER(10,2), "UNIT_COST" NUMBER(10,2), "UNIT_PRICE" NUMBER(10,2)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DATA_FILE_DIR" ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
TERRITORY AMERICA
BADFILE log_file_dir:'ext_1v3.bad'
LOGFILE log_file_dir:'ext_1v3.log'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
( PROD_ID ,
CUST_ID ,
TIME_ID DATE(10) "YYYY-MM-DD",
CHANNEL_ID ,
PROMO_ID ,
QUANTITY_SOLD ,
AMOUNT_SOLD ,
UNIT_COST ,
UNIT_PRICE
)
) LOCATION ( 'sale1v3.dat' ) ) REJECT LIMIT 100
25-NOV-21 00:48:47.504: ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
25-NOV-21 00:48:47.612: W-4 Completed 45 TABLE objects in 9 seconds
25-NOV-21 00:48:47.612: W-4 Completed by worker 1 1 TABLE objects in 5 seconds
25-NOV-21 00:48:47.612: W-4 Completed by worker 2 26 TABLE objects in 8 seconds
25-NOV-21 00:48:47.612: W-4 Completed by worker 3 17 TABLE objects in 5 seconds
25-NOV-21 00:48:47.612: W-4 Completed by worker 4 1 TABLE objects in 4 seconds
25-NOV-21 00:48:47.746: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
25-NOV-21 00:48:48.107: W-3 . . imported "HR"."REGIONS" 5.546 KB 4 rows in 1 seconds using direct_path
25-NOV-21 00:48:48.160: W-1 . . imported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows in 1 seconds using direct_path
25-NOV-21 00:48:48.200: W-3 . . imported "OE"."ORDER_ITEMS" 21.01 KB 665 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.206: W-3 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_C" 5.898 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.209: W-3 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_G" 13.61 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.239: W-4 . . imported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows in 1 seconds using direct_path
25-NOV-21 00:48:48.240: W-2 . . imported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows in 1 seconds using direct_path
25-NOV-21 00:48:48.329: W-1 . . imported "SH"."SALES":"SALES_Q1_1998" 1.413 MB 43687 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.330: W-1 . . imported "SH"."SALES":"SALES_H2_1997" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.335: W-1 . . imported "SH"."SALES":"SALES_H1_1997" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.338: W-1 . . imported "SH"."SALES":"SALES_1996" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.416: W-4 . . imported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.550: W-2 . . imported "SH"."SALES":"SALES_Q3_2000" 1.910 MB 58950 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.598: W-4 . . imported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.651: W-2 . . imported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.772: W-4 . . imported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.846: W-2 . . imported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows in 0 seconds using direct_path
25-NOV-21 00:48:48.906: W-4 . . imported "SH"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.013: W-2 . . imported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows in 1 seconds using direct_path
25-NOV-21 00:48:49.074: W-4 . . imported "HR"."COUNTRIES" 6.367 KB 25 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.159: W-4 . . imported "OE"."INVENTORIES" 21.76 KB 1112 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.160: W-4 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_G" 13.61 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.161: W-2 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_L" 8.039 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.211: W-4 . . imported "HR"."LOCATIONS" 8.437 KB 23 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.212: W-4 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_I" 9.296 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.287: W-4 . . imported "OE"."PROMOTIONS" 5.570 KB 2 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.292: W-4 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_T" 6.343 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.361: W-4 . . imported "HR"."JOB_HISTORY" 7.195 KB 10 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.363: W-2 . . imported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.6 KB 4500 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.432: W-4 . . imported "OE"."PRODUCT_INFORMATION" 73.05 KB 288 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.435: W-4 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_H" 9.328 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.453: W-2 . . imported "SH"."COSTS":"COSTS_Q4_2001" 278.5 KB 9011 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.571: W-2 . . imported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.575: W-2 . . imported "SH"."COSTS":"COSTS_Q2_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.577: W-2 . . imported "SH"."COSTS":"COSTS_Q3_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.579: W-2 . . imported "SH"."COSTS":"COSTS_Q4_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.581: W-2 . . imported "SH"."COSTS":"COSTS_Q1_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.598: W-2 . . imported "SH"."COSTS":"COSTS_Q2_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.600: W-2 . . imported "SH"."COSTS":"COSTS_Q3_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.603: W-2 . . imported "SH"."COSTS":"COSTS_Q4_2003" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.618: W-2 . . imported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.718: W-2 . . imported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.860: W-2 . . imported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.895: W-4 . . imported "SH"."CUSTOMERS" 10.27 MB 55500 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.948: W-2 . . imported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.952: W-2 . . imported "SH"."COSTS":"COSTS_H2_1997" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.956: W-2 . . imported "SH"."COSTS":"COSTS_H1_1997" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.960: W-2 . . imported "SH"."COSTS":"COSTS_1996" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:49.965: W-2 . . imported "SH"."COSTS":"COSTS_1995" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.056: W-2 . . imported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows in 1 seconds using direct_path
25-NOV-21 00:48:50.103: W-4 . . imported "OE"."ORDERS" 12.59 KB 105 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.107: W-4 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_I" 8.890 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.138: W-2 . . imported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.201: W-4 . . imported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.222: W-2 . . imported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.267: W-4 . . imported "SH"."PRODUCTS" 26.71 KB 72 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.305: W-2 . . imported "SH"."COSTS":"COSTS_Q4_2000" 160.4 KB 5088 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.423: W-2 . . imported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.443: W-2 . . imported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.534: W-2 . . imported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.632: W-2 . . imported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.730: W-2 . . imported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.734: W-2 . . imported "SH"."COSTS":"COSTS_Q1_2002" 7.242 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.810: W-2 . . imported "SH"."FWEEK_PSCAT_SALES_MV" 419.9 KB 11266 rows in 0 seconds using direct_path
25-NOV-21 00:48:50.989: W-2 . . imported "SH"."TIMES" 381.7 KB 1826 rows in 0 seconds using direct_path
25-NOV-21 00:48:51.134: W-2 . . imported "IX"."STREAMS_QUEUE_TABLE" 17.69 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:51.332: W-2 . . imported "SH"."PROMOTIONS" 59.17 KB 503 rows in 0 seconds using direct_path
25-NOV-21 00:48:51.617: W-4 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_S" 11.30 KB 4 rows in 1 seconds using external_table
25-NOV-21 00:48:59.396: W-4 . . imported "PM"."TEXTDOCS_NESTEDTAB" 87.85 KB 12 rows in 8 seconds using direct_path
25-NOV-21 00:48:59.399: W-4 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_H" 9.328 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.500: W-4 . . imported "SH"."COUNTRIES" 10.46 KB 23 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.510: W-3 . . imported "OE"."CATEGORIES_TAB" 14.43 KB 22 rows in 11 seconds using direct_path
25-NOV-21 00:48:59.604: W-4 . . imported "HR"."DEPARTMENTS" 7.125 KB 27 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.733: W-4 . . imported "HR"."JOBS" 7.109 KB 19 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.823: W-4 . . imported "HR"."EMPLOYEES" 17.08 KB 107 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.824: W-4 . . imported "IX"."AQ$_ORDERS_QUEUETABLE_T" 6.335 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.904: W-4 . . imported "SH"."CHANNELS" 7.414 KB 5 rows in 0 seconds using direct_path
25-NOV-21 00:48:59.971: W-4 . . imported "SH"."CAL_MONTH_SALES_MV" 6.382 KB 48 rows in 0 seconds using direct_path
25-NOV-21 00:49:00.118: W-1 . . imported "OE"."WAREHOUSES" 12.76 KB 9 rows in 12 seconds using external_table
25-NOV-21 00:49:00.121: W-1 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_L" 8.039 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:49:00.465: W-2 . . imported "PM"."PRINT_MEDIA" 190.6 KB 4 rows in 9 seconds using external_table
25-NOV-21 00:49:00.596: W-2 . . imported "IX"."ORDERS_QUEUETABLE" 21.33 KB 0 rows in 0 seconds using external_table
25-NOV-21 00:49:00.840: W-4 . . imported "IX"."AQ$_STREAMS_QUEUE_TABLE_S" 11.57 KB 1 rows in 0 seconds using external_table
25-NOV-21 00:49:02.130: W-3 . . imported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.57 KB 288 rows in 3 seconds using direct_path
25-NOV-21 00:49:09.040: W-1 . . imported "OE"."CUSTOMERS" 81.17 KB 319 rows in 9 seconds using direct_path
25-NOV-21 00:49:09.100: W-1 . . imported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.101: W-1 . . imported "SH"."SALES":"SALES_1995" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.125: W-3 . . imported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.656 KB 21 rows in 7 seconds using direct_path
25-NOV-21 00:49:09.234: W-3 . . imported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.237: W-3 . . imported "SH"."SALES":"SALES_Q1_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.238: W-3 . . imported "SH"."SALES":"SALES_Q2_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.239: W-3 . . imported "SH"."SALES":"SALES_Q3_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.240: W-3 . . imported "SH"."SALES":"SALES_Q4_2002" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.241: W-3 . . imported "SH"."SALES":"SALES_Q1_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.242: W-3 . . imported "SH"."SALES":"SALES_Q2_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.257: W-3 . . imported "SH"."SALES":"SALES_Q3_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.259: W-3 . . imported "SH"."SALES":"SALES_Q4_2003" 7.664 KB 0 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.342: W-3 . . imported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.425: W-3 . . imported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.503: W-3 . . imported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows in 0 seconds using direct_path
25-NOV-21 00:49:09.578: W-2 Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:49:09.824: W-4 Completed 35 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:49:09.824: W-4 Completed by worker 1 35 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:49:09.826: W-4 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
25-NOV-21 00:49:10.186: W-4 Completed 166 COMMENT objects in 1 seconds
25-NOV-21 00:49:10.186: W-4 Completed by worker 1 6 COMMENT objects in 0 seconds
25-NOV-21 00:49:10.186: W-4 Completed by worker 2 80 COMMENT objects in 1 seconds
25-NOV-21 00:49:10.186: W-4 Completed by worker 3 80 COMMENT objects in 1 seconds
25-NOV-21 00:49:10.188: W-4 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
25-NOV-21 00:49:10.376: W-2 Completed 1 FUNCTION objects in 0 seconds
25-NOV-21 00:49:10.376: W-2 Completed by worker 1 1 FUNCTION objects in 0 seconds
25-NOV-21 00:49:10.380: W-2 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
25-NOV-21 00:49:10.588: W-4 Completed 2 PROCEDURE objects in 0 seconds
25-NOV-21 00:49:10.588: W-4 Completed by worker 3 2 PROCEDURE objects in 0 seconds
25-NOV-21 00:49:10.591: W-4 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
25-NOV-21 00:49:10.787: W-2 Completed 1 ALTER_FUNCTION objects in 0 seconds
25-NOV-21 00:49:10.787: W-2 Completed by worker 1 1 ALTER_FUNCTION objects in 0 seconds
25-NOV-21 00:49:10.790: W-2 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
25-NOV-21 00:49:10.993: W-4 Completed 2 ALTER_PROCEDURE objects in 0 seconds
25-NOV-21 00:49:10.993: W-4 Completed by worker 3 2 ALTER_PROCEDURE objects in 0 seconds
25-NOV-21 00:49:10.996: W-4 Processing object type SCHEMA_EXPORT/VIEW/VIEW
25-NOV-21 00:49:11.656: W-2 Completed 21 VIEW objects in 0 seconds
25-NOV-21 00:49:11.656: W-2 Completed by worker 1 21 VIEW objects in 0 seconds
25-NOV-21 00:49:11.659: W-2 Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
25-NOV-21 00:49:11.841: W-3 Completed 5 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:49:11.841: W-3 Completed by worker 1 5 OBJECT_GRANT objects in 0 seconds
25-NOV-21 00:49:11.844: W-3 Processing object type SCHEMA_EXPORT/VIEW/COMMENT
25-NOV-21 00:49:12.040: W-2 Completed 4 COMMENT objects in 0 seconds
25-NOV-21 00:49:12.040: W-2 Completed by worker 4 4 COMMENT objects in 0 seconds
25-NOV-21 00:49:12.043: W-2 Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
25-NOV-21 00:49:12.342: W-3 Completed 3 TYPE_BODY objects in 0 seconds
25-NOV-21 00:49:12.342: W-3 Completed by worker 1 3 TYPE_BODY objects in 0 seconds
25-NOV-21 00:49:12.493: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
25-NOV-21 00:49:17.037: W-4 Completed 43 INDEX objects in 4 seconds
25-NOV-21 00:49:17.037: W-4 Completed by worker 1 11 INDEX objects in 4 seconds
25-NOV-21 00:49:17.037: W-4 Completed by worker 2 10 INDEX objects in 4 seconds
25-NOV-21 00:49:17.037: W-4 Completed by worker 3 11 INDEX objects in 4 seconds
25-NOV-21 00:49:17.037: W-4 Completed by worker 4 11 INDEX objects in 4 seconds
25-NOV-21 00:49:17.040: W-4 Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
25-NOV-21 00:49:17.392: W-2 Completed 1 INDEX objects in 0 seconds
25-NOV-21 00:49:17.392: W-2 Completed by worker 3 1 INDEX objects in 0 seconds
25-NOV-21 00:49:17.395: W-2 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
25-NOV-21 00:49:18.377: W-4 Completed 37 CONSTRAINT objects in 1 seconds
25-NOV-21 00:49:18.377: W-4 Completed by worker 1 37 CONSTRAINT objects in 1 seconds
25-NOV-21 00:49:18.379: W-4 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
25-NOV-21 00:49:18.906: W-3 Completed 30 REF_CONSTRAINT objects in 0 seconds
25-NOV-21 00:49:18.906: W-3 Completed by worker 1 30 REF_CONSTRAINT objects in 0 seconds
25-NOV-21 00:49:18.908: W-3 Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
25-NOV-21 00:49:21.718: W-3 Completed 15 INDEX objects in 3 seconds
25-NOV-21 00:49:21.718: W-3 Completed by worker 1 3 INDEX objects in 3 seconds
25-NOV-21 00:49:21.718: W-3 Completed by worker 2 4 INDEX objects in 3 seconds
25-NOV-21 00:49:21.718: W-3 Completed by worker 3 4 INDEX objects in 3 seconds
25-NOV-21 00:49:21.718: W-3 Completed by worker 4 4 INDEX objects in 3 seconds
25-NOV-21 00:49:21.721: W-3 Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
25-NOV-21 00:49:21.965: W-4 Completed 3 TRIGGER objects in 0 seconds
25-NOV-21 00:49:21.965: W-4 Completed by worker 1 3 TRIGGER objects in 0 seconds
25-NOV-21 00:49:21.968: W-4 Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
25-NOV-21 00:49:22.282: W-2 Completed 2 TRIGGER objects in 0 seconds
25-NOV-21 00:49:22.282: W-2 Completed by worker 1 2 TRIGGER objects in 0 seconds
25-NOV-21 00:49:22.285: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
25-NOV-21 00:49:24.233: W-4 Completed 1 INDEX objects in 2 seconds
25-NOV-21 00:49:24.233: W-4 Completed by worker 3 1 INDEX objects in 2 seconds
25-NOV-21 00:49:24.234: W-4 Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
25-NOV-21 00:49:25.499: W-2 Completed 2 MATERIALIZED_VIEW objects in 1 seconds
25-NOV-21 00:49:25.499: W-2 Completed by worker 1 2 MATERIALIZED_VIEW objects in 1 seconds
25-NOV-21 00:49:25.521: W-2 Processing object type SCHEMA_EXPORT/DIMENSION
25-NOV-21 00:49:25.780: W-4 Completed 5 DIMENSION objects in 0 seconds
25-NOV-21 00:49:25.780: W-4 Completed by worker 3 5 DIMENSION objects in 0 seconds
25-NOV-21 00:49:25.783: W-4 Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
25-NOV-21 00:49:26.569: W-2 Completed 15 PROCACT_INSTANCE objects in 1 seconds
25-NOV-21 00:49:26.569: W-2 Completed by worker 1 15 PROCACT_INSTANCE objects in 1 seconds
25-NOV-21 00:49:26.572: W-2 Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
25-NOV-21 00:49:26.939: W-3 Completed 10 PROCDEPOBJ objects in 0 seconds
25-NOV-21 00:49:26.939: W-3 Completed by worker 1 10 PROCDEPOBJ objects in 0 seconds
25-NOV-21 00:49:26.942: W-3 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
25-NOV-21 00:49:27.102: W-4 Completed 6 PROCOBJ objects in 1 seconds
25-NOV-21 00:49:27.102: W-4 Completed by worker 1 6 PROCOBJ objects in 1 seconds
25-NOV-21 00:49:27.104: W-4 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
25-NOV-21 00:49:27.295: W-2 Completed 1 PROCACT_SCHEMA objects in 0 seconds
25-NOV-21 00:49:27.295: W-2 Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
25-NOV-21 00:49:27.373: W-3 Completed 100 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 22 seconds
25-NOV-21 00:49:27.660: Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Thu Nov 25 00:49:27 2021 elapsed 0 00:01:00
有2个错误。第一个是关于"SH".“SALES_TRANSACTIONS_EXT”，它需要建立directory：DATA_FILE_DIR，并且外部文件sale1v3.dat也没有拷贝过来；第二个是XMLType “OE”.“PURCHASEORDER”，这里就不再处理了。
心得体会
所以我们看到，目标数据库上预先并没有建立hr，oe这些用户。因此用sys执行expdp会导出创建用户和用户赋权语句。这确实很方便。
从这里可知：
If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_DATABASE role, then you can export only your own schema.
SYS当然是具备DATAPUMP_EXP_FULL_DATABASE role的，但如果你用hr用户导出其自己的schema，你就需要在目标数据库上建立hr用户，并为hr赋权了。
实验2：Data Pump + Database Link实现Schema迁移
源数据库：db122-cdb上的orclpdb1中的所有用户Schema
目标数据库：db19-cdb上的orclpdb2
Database Link的好处是不用缓存和传递中间文件，坏处包括数据明文传输，元数据不能并行导入等。总之不建议。
建立目标数据库
使用实验1中的脚本CreateTargetCDB.sql建立空的PDB：orclpdb2。
在目标数据库建立指向源数据库的网络服务
最简单的方法是将源数据库服务器上的tnsnames.ora中的条目拷贝到目标数据库服务器上的tnsnames.ora中。为避免重名，我们改为CDB12_orclpdb1：
...
CDB12_orclpdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db122-cdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb1)
)
)
...
测试其可以连通：
$ tnsping CDB12_orclpdb1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-NOV-2021 01:17:21
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db122-cdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclpdb1)))
OK (0 msec)
在目标数据库建立指向源数据库的DB Link
DB link的名字为cdb12，建立在目标数据库orclpdb2中：
$ sqlplus system/Ora_DB4U@orclpdb2
system@ORCLPDB2> create database link cdb12 connect to system identified by Ora_DB4U using 'CDB12_orclpdb1';
Database link created.
system@ORCLPDB2> select * from global_name@cdb12;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCLPDB1
注意，这里的Database Link是用system用户建立的，因为后续我们也需要使用system来执行导出和导入。
使用Data Pump网络模式导入数据
导入参数文件如下：
[oracle@db19-cdb dumpdir]$ cat import.par
DIRECTORY=my_data_pump_dir
LOGFILE=logfile.log
SCHEMAS=HR,OE,PM,IX,SH,BI
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
REMAP_TABLESPACE=%:USERS
NETWORK_LINK=cdb12
执行导入：
[oracle@db19-cdb dumpdir]$ impdp system/Ora_DB4U@orclpdb2 parfile=import.par
扫一扫
203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
