SQL*LOADER支持异构，DATA PUMP只支持Oracle数据库
移动数据的方式
SQL*LOADER，DATA PUMP和外部表。
创建和使用DIRECTORY 对象
Oracle DIRECTORY 运行数据库读取操作系统文件，例如Data Pump。
数据库内部，Directory权限需要赋予用户；操作系统层面，Oracle用户需有访问目录的权限。
Directory的属主是SYS，但是拥有CREATE ANY DIRECTORY权限的用户可以创建Directory。
示例：
$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 17 18:19:11 2019
Version 19.3.0.0.0Copyright (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.0ORCLCDB> alter session set container=orclpdb1;Session altered.ORCLCDB> grant create session to user1 identified by Welcome1;Grant succeeded.ORCLCDB> connect user1/Welcome1@orclpdb1;
Connected.
orclpdb1> create directory dumpdir as '/home/oracle';
create directory dumpdir as '/home/oracle'
*
ERROR at line 1:
ORA-01031: insufficient privilegesorclpdb1> connect / as sysdba
Connected.
ORCLCDB> alter session set container=orclpdb1;Session altered.ORCLCDB> grant create any directory to user1;Grant succeeded.ORCLCDB> connect user1/Welcome1@orclpdb1;
Connected.
orclpdb1> create directory dumpdir as '/home/oracle';Directory created.orclpdb1> grant read on directory dumpdir to public;Grant succeeded.orclpdb1> grant write on directory dumpdir to hr;Grant succeeded.
-- 注意owner总是SYS
orclpdb1> select * from all_directories where directory_name='DUMPDIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
-------------------- -------------------- ------------------------------ -------------
SYS DUMPDIR /home/oracle 3orclpdb1> drop directory dumpdir;
drop directory dumpdir
*
ERROR at line 1:
ORA-01031: insufficient privilegesorclpdb1> connect / as sysdba
Connected.
ORCLCDB> alter session set container=orclpdb1;Session altered.ORCLCDB> drop directory dumpdir;Directory dropped.使用SQL*LOADER从非Oracle数据库加载数据
使用SQL*Loader
SQLLoader是客户端程序，可批量导入第三方的数据。
SQLLoader需要输入数据文件，控制文件（解析输入数据文件的格式）和reject文件（成功解析但被数据库拒绝的数据）。控制文件中也可以放数据，但不建议，因为需要重用控制文件。
使用SQL*Loader
插入数据有传统和direct path两种方式。
传统方式通过数据库buffer cache，构建传统的insert语句，产生undo和redo；
direct path方式跳过数据库buffer cache，直接写到数据文件，不产生undo，可选择不产生redo，因此很快，性能影响小。但缺点是：
- 操作期间必须删除或禁用参照一致性约束
- 不会触发插入触发器，但支持UNIQUE, PRIMARY KEY和NOT NULL约束
- 操作期间，表被锁，禁止DML操作
- clustered table不能用
示例：
orclpdb1> desc departments;Name Null? Type----------------------------------------- -------- ----------------------------DEPARTMENT_ID NOT NULL NUMBER(4)DEPARTMENT_NAME NOT NULL VARCHAR2(30)MANAGER_ID NUMBER(6)LOCATION_ID NUMBER(4)orclpdb1> create table dept(deptno number(2) not null, dname varchar2(14), loc varchar2(13));Table created.orclpdb1> desc dept;Name Null? Type----------------------------------------- -------- ----------------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)$ cat dept.dat
60,CONSULTING,TORONTO
70,HR,OXFORD
80,EDUCATION,$ cat depts.ctl
load data
infile 'dept.dat'
badfile 'dept.bad'
discardfile 'dept.dsc'
append
into table dept
fields terminated by ','
trailing nullcols
(deptno integer external(2),
dname,
loc)$ sqlldr userid=hr/oracle@orclpdb1 control=depts.ctlSQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:01:05 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Path used: Conventional
Commit point reached - logical record count 3Table DEPT:3 Rows successfully loaded.Check the log file:depts.log
for more information about the load.SQL*Loader Express模式
简单模式，不需要控制文件。但有要求：
- 数据文件后缀为dat，前缀为表名
- 列类型必须为字符，数字或日期
- 逗号分割，不能用双引号括起
- 每一列都有值
- 用户必须有CREATE ANY DIRECTORY权限
$ sqlldr hr/oracle@orclpdb1 table=deptSQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:13:43 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Express Mode Load, Table: DEPT
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_0 0000 for file dept.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using v alue of NONE
Express Mode Load, Table: DEPT
Path used: DirectLoad completed - logical record count 3.Table DEPT:2 Rows successfully loaded.Check the log file:dept.log
for more information about the load.在dept.log中会包含控制文件：
...
Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'dept'
APPEND
INTO TABLE DEPT
FIELDS TERMINATED BY ","
(DEPTNO,DNAME,LOC
)
End of generated control file for possible reuse.Record 3: Rejected - Error on table DEPT, column LOC.
Column not found before end of logical record (use TRAILING NULLCOLS)Table DEPT:2 Rows successfully loaded.1 Row not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.0 Rows not loaded because all fields were null.
...
使用外部表移动数据
外部表通过Oracle directory 访问，可以SELECT，不能修改。
从外部表导入数据避免了复杂的ETL过程。语法：
CREATE TABLE ... ORGANIZATION EXTERNAL
示例，涵盖Directory，SQL*Loader，外部表：
orclpdb1> create table names(first varchar2(10), last varchar(10));Table created.
$ cat names.txt
John,Watson
Allen,Zhou
Jason,Huang
$ cat names.ctl
load data
infile 'names.txt'
badfile 'names.bad'
truncate
into table names
fields terminated by ','
trailing nullcols
(first, last)$ sqlldr hr/oracle@orclpdb1 control=names.ctlSQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:28:00 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Path used: Conventional
Commit point reached - logical record count 3Table NAMES:3 Rows successfully loaded.Check the log file:names.log
for more information about the load.-- 生成创建外部表的语句
$ sqlldr hr/oracle@orclpdb1 control=names.ctl external_table=generate_only
SQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:28:45 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Path used: External Table$ cat names.logSQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:28:45 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Control File: names.ctl
Data File: names.txtBad File: names.badDiscard File: none specified(Allow all discards)Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External TableTable NAMES, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effectColumn Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FIRST FIRST * , CHARACTER
LAST NEXT * , CHARACTERCREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_NAMES"
("FIRST" VARCHAR2(10),"LAST" VARCHAR2(10)
)
ORGANIZATION external
(TYPE oracle_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'names.bad'LOGFILE 'names.log_xt'READSIZE 1048576FIELDS TERMINATED BY "," LDRTRIMMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS("FIRST" CHAR(255)TERMINATED BY ",","LAST" CHAR(255)TERMINATED BY ","))location('names.txt')
)REJECT LIMIT UNLIMITEDINSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO NAMES
(FIRST,LAST
)
SELECT"FIRST","LAST"
FROM "SYS_SQLLDR_X_EXT_NAMES"statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_NAMES"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000Run began on Thu Oct 17 20:28:45 2019
Run ended on Thu Oct 17 20:28:45 2019Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.03-- 执行其中的CREATE DIRECTORY和CREATE TABLE语句
orclpdb1> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle';Directory created.CREATE TABLE "SYS_SQLLDR_X_EXT_NAMES"
("FIRST" VARCHAR2(10),"LAST" VARCHAR2(10)
)
ORGANIZATION external
(TYPE oracle_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'names.bad'LOGFILE 'names.log_xt'READSIZE 1048576FIELDS TERMINATED BY "," LDRTRIMMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS("FIRST" CHAR(255)TERMINATED BY ",","LAST" CHAR(255)TERMINATED BY ","))location('names.txt')30 )REJECT LIMIT UNLIMITED31 ;Table created.orclpdb1> select * from SYS_SQLLDR_X_EXT_NAMES;FIRST LAST
---------- ----------
John Watson
Allen Zhou
Jason HuangDATA PUMP架构
Data Pump是服务器端程序。所有的工作由服务器端进行完成，不需要会话，性能好于SQLLoader，可以后台运行。
Data Pump程序为expdp 和impdp, 产生的文件包括SQL文件（DDL），dump文件和日志文件。
Data Pump会生成control file，记录任务的进度。
Data Pump有两种数据加载和卸载方法：direct path 和external table path。direct path 会跳过buffer cache，而后者不会。这和SQLLoader类似。使用哪一种由Data Pump自己决定。
使用DATA PUMP在Oracle数据库间传递数据
Data Pump适合于大数据量导入导出，也可以导出DDL。产生的文件是特定格式，只有Data Pump自己能解析。
Data Pump包括5种模式。分布为full，schema（默认模式）, tables, tablespace（表空间中的对象）, Transportable Tablespace。各模式的示例见这里。
语法帮助：
$ impdp help=y
能力
- 可细粒度控制，导出整个或部分数据库
- 对于表，可加where条件，或随机采样
- 可并行
- 可估算需要的空间
- 可通过Database Link在两个数据库间导入导出，无需落地
- 可remap，如表空间，schema
- 导出时，可压缩加密
Data Pump命令行
orclpdb1> create directory datadir1 as '/home/oracle';Directory created.orclpdb1> create directory datadir2 as '/home/oracle';Directory created.$ expdp system/Welcome1@orclpdb1 full=y parallel=2 dumpfile=datadir1:full1_%U.dmp,datadir2:full2_%U.dmp filesize=2g compression=allExport: Release 19.0.0.0.0 - Production on Thu Oct 17 21:35:21 2019
Version 19.3.0.0.0Copyright (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 "SYSTEM"."SYS_EXPORT_FULL_01": system/********@orclpdb1 full=y parallel=2 dumpfile=datadir1:full1_%U.dmp,datadir2:full2_%U.dmp filesize=2g compression=all
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P301" 813.7 KB 1211 rows
. . exported "SYSTEM"."REDO_DB" 7.25 KB 1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 5.960 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 6.007 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 5.195 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 5.195 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 5.117 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 4.992 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 5.015 KB 1 rows
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.296 KB 45 rows
. . exported "LBACSYS"."OLS$PROPS" 5.132 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 4.984 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 4.921 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 4.882 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 4.937 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 4.960 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 4.843 KB 2 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 5.156 KB 3 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 4.921 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "OSGPC"."JY_RECHARGE" 1.597 MB 108295 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SH"."CUSTOMERS" 2.260 MB 55500 rows
. . exported "SYS"."NACL$_HOST_EXP" 5.132 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
. . exported "OSGPC"."AC_EXP_RECORD" 1.307 MB 7814 rows
. . exported "OSGPC"."YW_POWERFEE_ORDER" 2.933 MB 12714 rows
. . exported "OSGPC"."ACCOUNT_BILL_RECORD" 973.4 KB 16103 rows
. . exported "OSGPC"."AC_MQ_MESSAGE" 1.164 MB 23921 rows
. . exported "OSGPC"."AC_RECORD" 865.4 KB 10909 rows
. . exported "OSGPC"."SALE_ORDER" 629.2 KB 14106 rows
. . exported "OE"."PRODUCT_DESCRIPTIONS" 636.6 KB 8640 rows
. . exported "SH"."SALES":"SALES_Q4_2001" 216.2 KB 69749 rows
. . exported "SH"."SALES":"SALES_Q3_1999" 194.2 KB 67138 rows
. . exported "SH"."SALES":"SALES_Q3_2001" 194.7 KB 65769 rows
. . exported "SH"."SALES":"SALES_Q2_2001" 183.3 KB 63292 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 196.8 KB 64186 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 184.9 KB 60608 rows
. . exported "SH"."SALES":"SALES_Q4_1999" 183.3 KB 62388 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 177.1 KB 62197 rows
. . exported "SH"."SALES":"SALES_Q3_2000" 170.6 KB 58950 rows
. . exported "SH"."SALES":"SALES_Q4_2000" 163.4 KB 55984 rows
. . exported "SH"."SALES":"SALES_Q2_2000" 156.8 KB 55515 rows
. . exported "SH"."SALES":"SALES_Q2_1999" 160.6 KB 54233 rows
. . exported "SH"."SALES":"SALES_Q3_1998" 148.9 KB 50515 rows
. . exported "SH"."SALES":"SALES_Q4_1998" 146.3 KB 48874 rows
. . exported "OSGPC"."SALE_ORDER_DETAIL" 264.0 KB 14109 rows
. . exported "SH"."SALES":"SALES_Q1_1998" 137.7 KB 43687 rows
. . exported "OSGPC"."AC_FAIL_RECORD" 261.7 KB 6907 rows
. . exported "SH"."SALES":"SALES_Q2_1998" 109.5 KB 35758 rows
. . exported "OSGPC"."WG_BUSIFEE_ORDER" 232.7 KB 1407 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 187.7 KB 4500 rows
. . exported "OSGPC"."COST_CONTROL_SIGN_APPLY" 196.0 KB 2808 rows
. . exported "OSGPC"."ELE_PAY_HIS_RECORD" 220.1 KB 3182 rows
. . exported "OSGPC"."ACCOUNT_BILL_DETAIL" 130.4 KB 3357 rows
. . exported "SH"."TIMES" 47.67 KB 1826 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV" 85.13 KB 11266 rows
...
. . exported "WMSYS"."WM$EXP_MAP" 5.273 KB 3 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER". . exported "OE"."PURCHASEORDER" 56.53 KB 132 rows
. . exported "OE"."PRODUCT_INFORMATION" 25.85 KB 288 rows
. . exported "OE"."CUSTOMERS" 26.54 KB 319 rows
...
. . exported "SH"."PROMOTIONS" 15.38 KB 503 rows
. . exported "SH"."PRODUCTS" 9.156 KB 72 rows
. . exported "PM"."PRINT_MEDIA" 158.6 KB 4 rows
. . exported "OE"."ORDER_ITEMS" 9.281 KB 665 rows
. . exported "OE"."INVENTORIES" 8.218 KB 1112 rows
. . exported "HR"."EMPLOYEES" 8.796 KB 107 rows
. . exported "PM"."TEXTDOCS_NESTEDTAB" 39.16 KB 12 rows
...
. . exported "OE"."ORDERS" 7.562 KB 105 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_S" 5.835 KB 1 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_S" 5.937 KB 4 rows
. . exported "SH"."COUNTRIES" 6.085 KB 23 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_H" 0 KB 0 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_I" 0 KB 0 rows
. . exported "OE"."CATEGORIES_TAB" 7.156 KB 22 rows
. . exported "HR"."LOCATIONS" 6.046 KB 23 rows
. . exported "SH"."CHANNELS" 5.265 KB 5 rows
. . exported "HR"."JOB_HISTORY" 5.304 KB 10 rows
. . exported "HR"."JOBS" 5.437 KB 19 rows
. . exported "OE"."WAREHOUSES" 6.812 KB 9 rows
. . exported "HR"."DEPARTMENTS" 5.437 KB 27 rows
. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 5.015 KB 21 rows
. . exported "SH"."CAL_MONTH_SALES_MV" 5.226 KB 48 rows
. . exported "HR"."REGIONS" 4.851 KB 4 rows
. . exported "OE"."PROMOTIONS" 4.859 KB 2 rows
. . exported "HR"."NAMES" 4.835 KB 3 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_G" 0 KB 0 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_L" 0 KB 0 rows
. . exported "IX"."AQ$_ORDERS_QUEUETABLE_T" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_C" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_G" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_H" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_I" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_L" 0 KB 0 rows
. . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_T" 0 KB 0 rows
. . exported "IX"."ORDERS_QUEUETABLE" 0 KB 0 rows
. . exported "HR"."COUNTRIES" 5.226 KB 25 rows
. . exported "IX"."STREAMS_QUEUE_TABLE" 0 KB 0 rows
...
. . exported "SH"."COSTS":"COSTS_1995" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_1996" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1995" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1996" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:/home/oracle/full1_01.dmp/home/oracle/full2_01.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Thu Oct 17 21:39:33 2019 elapsed 0 00:04:11$ ll *.dmp
-rw-r-----. 1 oracle oinstall 2002944 Oct 17 21:39 full1_01.dmp
-rw-r-----. 1 oracle oinstall 23015424 Oct 17 21:39 full2_01.dmp导入部分schema:
$ impdp system/Welcome1@orclpdb1 directory=datadir1 schemas=osgpc dumpfile=full1_01.dmp,full2_01.dmp
在这里插入代码片
表空间导出导入
过程如下：
- 源表空间设为只读
- 导出表空间的元数据
- 将datafield和导出的元数据拷贝到对方
- 导入元数据
- 源和目标表空间均改为读写
如果源和目标平台字节序不一致，还需要使用RMAN CONVERT转换字节序。如：
orclpdb1> select * from v$transportable_platform order by platform_name;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ---------------------------------------- -------------- ----------6 AIX-Based Systems (64-bit) Big 016 Apple Mac OS Big 021 Apple Mac OS (x86-64) Little 019 HP IA Open VMS Little 015 HP Open VMS Little 05 HP Tru64 UNIX Little 03 HP-UX (64-bit) Big 04 HP-UX IA (64-bit) Big 018 IBM Power Based Linux Big 09 IBM zSeries Based Linux Big 010 Linux IA (32-bit) Little 011 Linux IA (64-bit) Little 022 Linux OS (S64) Big 013 Linux x86 64-bit Little 07 Microsoft Windows IA (32-bit) Little 08 Microsoft Windows IA (64-bit) Little 012 Microsoft Windows x86 64-bit Little 017 Solaris Operating System (x86) Little 020 Solaris Operating System (x86-64) Little 01 Solaris[tm] OE (32-bit) Big 02 Solaris[tm] OE (64-bit) Big 021 rows selected.限制：
- 表空间必须是自包含的。也即表空间中的对象是完整的，不依赖于其它对象。如果表的数据和索引位于不同表空间，则两个表空间均需操作。
- 源和目标字符集一致。
- 对象的schema。在目标数据库必须存在
- 目标端不能存在同名的表空间
- 如果目标端对象存在，会被跳过而不是覆盖
Data Pump示例1，迁移Schema:
-- 创建用户user1，user2，在PDB: orclpdb1中
grant dba to user1 identified by Welcome1;
grant dba to user2 identified by Welcome1;
-- 创建源表
create table user1.users as select * from all_users;
create index user1.ui on user1.users(user_id);-- 找出默认的dump directory
orclpdb1> select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
directory_path--------------------------------------------------------------------------------
/opt/oracle/admin/ORCLCDB/dpdump/94B31C5BDD3F055EE0530100007FAE63-- 导出数据
$ expdp system/Welcome1@orclpdb1 schemas=user1 dumpfile=user1.dmpExport: Release 19.0.0.0.0 - Production on Fri Oct 18 21:29:27 2019
Version 19.3.0.0.0Copyright (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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orclpdb1 schemas=user1 dumpfile=user1.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "USER1"."USERS" 10.87 KB 46 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:/opt/oracle/admin/ORCLCDB/dpdump/94B31C5BDD3F055EE0530100007FAE63/user1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 18 21:31:49 2019 elapsed 0 00:01:58
-- 导入数据
$ impdp system/Welcome1@orclpdb1 remap_schema=user1:user2 dumpfile=user1.dmpImport: Release 19.0.0.0.0 - Production on Fri Oct 18 21:32:35 2019
Version 19.3.0.0.0Copyright (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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orclpdb1 remap_schema=user1:user2 dumpfile=user1.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USER2" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."USERS" 10.87 KB 46 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Oct 18 21:34:38 2019 elapsed 0 00:01:58-- 验证：
orclpdb1> select object_name, object_type from dba_objects where owner='USER2';OBJECT_NAM OBJECT_TYPE
---------- -----------------------
USERS TABLE
UI INDEXData Pump示例2, Transportable Tablespace:
ORCLCDB> create tablespace TS1;Tablespace created.
ORCLCDB> create user user1 default tablespace TS1 quota unlimited on TS1;User created.
ORCLCDB> create table user1.tab1 as select * from dba_users;Table created.
ORCLCDB> exec dbms_tts.transport_set_check('TS1');PL/SQL procedure successfully completed.
ORCLCDB> alter tablespace ts1 read only;Tablespace altered.
$ expdp system/Welcome1@orclpdb1 transport_tablespaces=ts1 dumpfile=ts1.dmpExport: Release 19.0.0.0.0 - Production on Fri Oct 18 22:58:17 2019
Version 19.3.0.0.0Copyright (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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@orclpdb1 transport_tablespaces=ts1 dumpfile=ts1.dmp
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLEMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:/opt/oracle/admin/ORCLCDB/dpdump/94B31C5BDD3F055EE0530100007FAE63/ts1.dmp
******************************************************************************
Datafiles required for transportable tablespace TS1:/u01/oradata/ORCLCDB/94B31C5BDD3F055EE0530100007FAE63/datafile/o1_mf_ts1_gtmmf04o_.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 18 23:02:22 2019 elapsed 0 00:03:59
-- 注意最后会给出需要迁移的数据文件
$ cp o1_mf_ts1_gtmmf04o_.dbf /opt/oracle/oradata/ORCLCDB2/ORCLPDB2
-- 在目标端创建用户user1，否则会报错说没有此用户
-- 还需要将dump文件拷贝到目标数据库实例的dump目录，此处略
$ impdp system/Welcome1@orclpdb2 dumpfile=ts1.dmp transport_datafiles=/tmp/o1_mf_ts1_gtmmf04o_.dbfImport: Release 19.0.0.0.0 - Production on Fri Oct 18 23:20:51 2019
Version 19.3.0.0.0Copyright (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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@orclpdb2 dumpfile=ts1.dmp transport_datafiles=/tmp/o1_mf_ts1_gtmmf04o_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 18 23:21:50 2019 elapsed 0 00:00:53
orclpdb2> alter tablespace ts1 read write;Tablespace altered.orclpdb2> select count(*) from user1.tab1;COUNT(*)
----------45
-- 最后记得将源端的表空间也改为可写
orclpdb1> alter tablespace ts1 read write;Tablespace altered.如果源和目标平台的字节序不同，还需要通过RMAN进行转换：
可以在源端转换：
convert datafile '.../ts1.dbf' to platform 'target_platform' format 'new_file_name';
或者在目标端转换：
convert datafile '.../ts1.dbf' from platform 'source_platform' format 'new_file_name';
多租户环境下的DATA PUMP
由于Data Pump是逻辑备份，因此PDB之间，PDB和Non-CDB之间都完全支持，除了不能导入CDB$ROOT外。关键是要用pdb的服务名。
Non-CDB到PDB
例如从11g升级到12c。目标PDB必须存在。以下为示例，为简便，源库为12c Non-CDB。
首先创建源数据库ORCLCDB2：
dbca -silent -createDatabase \-templateName General_Purpose.dbc \-gdbname ORCLCDB2 -sid ORCLCDB2 -responseFile NO_VALUE \-characterSet AL32UTF8 \-sysPassword Welcome1 \-systemPassword Welcome1 \-createAsContainerDatabase false \-pdbAdminPassword Welcome1 \-databaseType MULTIPURPOSE \-automaticMemoryManagement false \-totalMemory 1536 \-storageType FS \-datafileDestination "/opt/oracle/oradata/" \-redoLogFileSize 50 \-emConfiguration NONE \-ignorePreReqs
Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
50% complete
54% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:/opt/oracle/cfgtoollogs/dbca/ORCLCDB2.
Database Information:
Global Database Name:ORCLCDB2
System Identifier(SID):ORCLCDB2
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB2/ORCLCDB2.log" for further details.导出源库(所有操作在源库环境下)：
$ cd /u01/app/oracle
$ mkdir datapump
$ sqlplus / as sysdba
SQL> select name from v$tablespace;
NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMPSQL> quit
$ expdp system/Welcome1@orclcdb2 full=y directory=dpump dumpfile=noncdb_exp.dmp导入到目标库ORCLCDB（所有操作在目标库环境）：
$ sqlplus / as sysdba
最后删除源库：
dbca -silent -deleteDatabase -sourceDB orclcbd2 -sysDBAUserName sys -sysDBAPassword Welcome1
PDB到PDB
PDB到Non-CDB
支持所有类型的导出：full, conventional, schema, and transportable，除了不能将common user的对象导入Non-CDB，变通方法是使用REMAP_SCHEMA。
导出PDB - orclpdb1：
$ expdp system/Welcome1@orclpdb1 full=y dumpfile=pdb_exp.dmp
...
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:/opt/oracle/admin/ORCLCDB/dpdump/94B31C5BDD3F055EE0530100007FAE63/pdb_exp.dmp
$ cd /opt/oracle/admin/ORCLCDB/dpdump/94B31C5BDD3F055EE0530100007FAE63/
$ ll pdb_exp.dmp
-rw-r-----. 1 oracle oinstall 166055936 Oct 20 20:24 pdb_exp.dmp
将dump文件拷贝至目标数据库ORCLCDB2的DATA_PUMP_DIR目录下：
$ mv pdb_exp.dmp /opt/oracle/admin/ORCLCDB2/dpdump/
导入：
在这里插入代码片
Full Transportable Export and Import
白皮书。
实验参见这篇文章：Full Transportable Export/Import实验。
Transporting a Database Over the Network
就是利用Database Link直接传输元数据dump文件，但数据文件不行。
多租户环境下的SQL*LOADER
无甚区别，把服务名写对就好
参考
- Database Configuration Assistant (DBCA) : Creating Databases in Silent Mode
- Transporting FULL Database from 11g Database to 12c Database
