OCA/OCP Oracle 数据库12c考试指南读书笔记：第17章: Moving and Re-organizing Data

最新推荐文章于 2022-08-21 17:16:32 发布
最新推荐文章于 2022-08-21 17:16:32 发布
阅读量790 收藏
点赞数
分类专栏： Oracle 19c 文章标签： oracle 12c OCP
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/stevensxiao/article/details/102612308
版权

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 sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 17 18:19:11 2019
Version 19.3.0.0.0

Copyright (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.0

ORCLCDB> 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 privileges


orclpdb1> 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                               3

orclpdb1> drop directory dumpdir;
drop directory dumpdir
*
ERROR at line 1:
ORA-01031: insufficient privileges


orclpdb1> 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，因此很快，性能影响小。但缺点是：

  1. 操作期间必须删除或禁用参照一致性约束
  2. 不会触发插入触发器，但支持UNIQUE, PRIMARY KEY和NOT NULL约束
  3. 操作期间，表被锁，禁止DML操作
  4. 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.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:01:05 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table 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=dept

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:13:43 2019
Version 19.3.0.0.0

Copyright (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:      Direct

Load 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.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:28:00 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table 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.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      External Table

$ cat names.log

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Oct 17 20:28:45 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Control File:   names.ctl
Data File:      names.txt
  Bad File:     names.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table NAMES, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FIRST                               FIRST     *   ,       CHARACTER
LAST                                 NEXT     *   ,       CHARACTER



CREATE 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_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'names.bad'
    LOGFILE 'names.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "FIRST" CHAR(255)
        TERMINATED BY ",",
      "LAST" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'names.txt'
  )
)REJECT LIMIT UNLIMITED


INSERT 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_00000



Run began on Thu Oct 17 20:28:45 2019
Run ended on Thu Oct 17 20:28:45 2019

Elapsed 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_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'names.bad'
    LOGFILE 'names.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "FIRST" CHAR(255)
        TERMINATED BY ",",
      "LAST" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'names.txt'
  )
 30  )REJECT LIMIT UNLIMITED
 31  ;

Table created.

orclpdb1> select * from SYS_SQLLDR_X_EXT_NAMES;

FIRST      LAST
---------- ----------
John       Watson
Allen      Zhou
Jason      Huang

DATA 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=all

Export: Release 19.0.0.0.0 - Production on Thu Oct 17 21:35:21 2019
Version 19.3.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
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

在这里插入代码片

表空间导出导入

过程如下：

  1. 源表空间设为只读
  2. 导出表空间的元数据
  3. 将datafield和导出的元数据拷贝到对方
  4. 导入元数据
  5. 源和目标表空间均改为读写
    如果源和目标平台字节序不一致，还需要使用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                     0
         16 Apple Mac OS                             Big                     0
         21 Apple Mac OS (x86-64)                    Little                  0
         19 HP IA Open VMS                           Little                  0
         15 HP Open VMS                              Little                  0
          5 HP Tru64 UNIX                            Little                  0
          3 HP-UX (64-bit)                           Big                     0
          4 HP-UX IA (64-bit)                        Big                     0
         18 IBM Power Based Linux                    Big                     0
          9 IBM zSeries Based Linux                  Big                     0
         10 Linux IA (32-bit)                        Little                  0
         11 Linux IA (64-bit)                        Little                  0
         22 Linux OS (S64)                           Big                     0
         13 Linux x86 64-bit                         Little                  0
          7 Microsoft Windows IA (32-bit)            Little                  0
          8 Microsoft Windows IA (64-bit)            Little                  0
         12 Microsoft Windows x86 64-bit             Little                  0
         17 Solaris Operating System (x86)           Little                  0
         20 Solaris Operating System (x86-64)        Little                  0
          1 Solaris[tm] OE (32-bit)                  Big                     0
          2 Solaris[tm] OE (64-bit)                  Big                     0

21 rows selected.

限制：

  1. 表空间必须是自包含的。也即表空间中的对象是完整的，不依赖于其它对象。如果表的数据和索引位于不同表空间，则两个表空间均需操作。
  2. 源和目标字符集一致。
  3. 对象的schema。在目标数据库必须存在
  4. 目标端不能存在同名的表空间
  5. 如果目标端对象存在，会被跳过而不是覆盖

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.dmp

Export: Release 19.0.0.0.0 - Production on Fri Oct 18 21:29:27 2019
Version 19.3.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
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.dmp

Import: Release 19.0.0.0.0 - Production on Fri Oct 18 21:32:35 2019
Version 19.3.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
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 exists

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/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         INDEX

Data 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.dmp

Export: Release 19.0.0.0.0 - Production on Fri Oct 18 22:58:17 2019
Version 19.3.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
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/TABLE

Master 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_.dbf

Import: Release 19.0.0.0.0 - Production on Fri Oct 18 23:20:51 2019
Version 19.3.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
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
TEMP

SQL> 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

无甚区别，把服务名写对就好

参考

  1. Database Configuration Assistant (DBCA) : Creating Databases in Silent Mode
  2. Transporting FULL Database from 11g Database to 12c Database
dingdingfish
关注
专栏目录
OCP OCA认证考试指南全册 Oracle 11g PDF 下载
01-11
### OCP OCA认证考试指南全册 Oracle 11g PDF 下载 #### Oracle认证简介 Oracle认证是针对Oracle数据库管理、开发等技术领域的一种专业认证。它为专业人士提供了证明其在Oracle技术方面具备深厚专业知识的机会。...
OCA / OCP Practice Tests: Exam 1Z0-808 and Exam 1Z0-809
03-28
OCA/OCP Java SE 8 Programmer Practice Tests complements the Sybex OCA: Oracle Certified Associate Java SE 8 Programmer I Certification Study Guide and the OCP: Oracle Certified Professional Java SE 8 ...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第11章: Subqueries and Set Operators
In-Memory Computing Technology
10-14 309
子查询是将多个SELECT语句变为一个的技术之一。子查询的输出可作为另一SELECT语句的输入。 定义子查询 子查询是嵌套到SELECT, INSERT, UPDATE，DELETE或其它子查询中的查询。 子查询可返回一行或多行，前者称为scalar 子查询。 子查询可用在： SELECT列表 FROM子句 WHERE子句 HAVING子句 子查询也称为inner查询。使用inner查询的查询...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第12章: Architectural Overview of Oracle Database 12c
In-Memory Computing Technology
10-14 595
Oracle Database Server包括实例和数据库，实例包括内存结构和进程，数据库包括磁盘文件。 逻辑结构如表与物理结构如datafiles之间，是通过controlfile和数据字典建立联系的。 架构组件 单实例数据库架构 包括实例和数据库。数据库是持久的，而实例的生命周期是暂时的，可停止可启动。 实例包括后台进程和内存结构。内存机构由操作系统以shared memory segmen...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第14章: Oracle Networking
In-Memory Computing Technology
10-15 545
Oracle的客户端/服务器架构是通过Oracle Net建立会话，Oracle Net可通过CLI或GUI配置，最终生成文本文件。 配置Oracle Net服务 用户不能直接访问数据库，而是通过服务器进程。客户端进程产生SQL，服务器进程执行SQL。 Oracle Net与C/S架构 Oracle Net提供一种机制，即启动一个服务器进程代表客户端进程执行代码，这建立了会话，Oracle Net...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第13章:Instance Management
In-Memory Computing Technology
10-14 425
数据库实例启动后，监听可能未启动。Database Express依赖监听。 Database Express无需配置，只需创建，在创建数据库时已创建。 数据库实例的配置依赖于初始化参数。 连接数据库实例可以是普通用户或特权用户。前者依赖于数据字典中存放的口令认证，后者依赖存放于外部的口令文件或通过操作系统认证。 使用数据库管理工具 常用的有SQL*Plus命令行或Database Express...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第33章：Managing Storage, Security, Availability, Performance in CDB/PDB
In-Memory Computing Technology
01-04 678
多租户环境下的空间管理 多租户环境下，表空间和数据文件都是与容器关联的，或者是CDB或者是PDB。 表空间架构 容器ID和表空间名唯一确定表空间。 SQL> select con_id, c.name c, t.name t, d.name d from v$containers c join v$tablespace t using (con_id) join v$datafile d u...
OCP/OCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053)_Part4/4
08-07
OCP/OCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053) 中文版 一共四个压缩分卷，该文档为第4部分。 一部分全下载之后，解压缩即可。
OCP/OCA认证考试指南全册：Oracle Database 11g(1Z0—051，120-052，120-053)
02-02
《OCP／OCA认证考试指南全册：Oracle Database 11g(1Z0—051，120-052，120-053)》是为OCA和OCP考试准备的独家权威指南。本书的每一章都提供吸引人的练习题、实践问题和本章知识点回顾以强化所学的知识。本书既是...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第21章: The Scheduler
In-Memory Computing Technology
11-17 416
使用Scheduler简化管理任务 Scheduler 架构 Scheduler 对象 Jobs Programs Schedules 自包含Job 使用 JOB CHAINS 执行系列相关任务 在远程系统使用Scheduler JOBS 使用高级 SCHEDULER FEATURES TO PRIORITIZE JOBS Using Job Classes Using Windows ...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第9章: Group Functions
In-Memory Computing Technology
10-12 495
应是统计函数。
OCA/OCP Oracle 数据库12c考试指南读书笔记：第8章: Single-Row and Conversion Functions
In-Memory Computing Technology
09-17 303
描述和使用字符、数字和日期函数 函数时一个黑盒，包含3要素，入参，封装实现，返回一个值 函数分为一次处理一行和一次处理多行两种（aggregation 或group函数）。 函数可嵌套 大小写转换：lower, upper, initcap SQL> select lower(sysdate), upper(sysdate), initcap('good') from dual; LOWE...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第5章: DDL and Schema Objects
In-Memory Computing Technology
09-10 532
客户端工具 SQL*Plus: 字符界面/C程序/只管理Oracle数据库 SQL Developer:图形界面/Java程序/功能丰富,可管理Oracle/TimesTen/MySQL等数据库 sqlplus 位于$ORACLE_HOME/bin下。 一些连接的例子： $ sqlplus /nolog # 使用别名，需要listener，需要tnsnames.ora中定义 $ sqlplus s...
QUESTION 36-SQL*Loader
jisen_huang的专栏
02-04 242
QUESTION 36 On your Oracle 12c database, you invoked SQL*Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command: $> sqlldr hr/hr@pdb table=employees Which two statements are true regarding the command? (Choose two
[2021-01-06] Oracle19C RAC 搭建OGG聚合模式同步数据到11.2.0.4详细记录
m15217321304的博客
01-06 3827
环境说明 源端19.7 RAC OGG版本19.1 node1: 192.168.40.160 node2: 192.168.40.161 RAC安装OGG使用的是ACFS方式，ACFS搭建可以参考： https://blog.csdn.net/m15217321304/article/details/112236321 19.1OGG在19.7RAC上面安装可以参考： https://blog.csdn.net/m15217321304/article/details/112245469 目标端.
oracle数据库常用命令一详细版，带图
weixin_39568073的博客
03-13 1166
创建PDB2 CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY aabb123 FILE_NAME_CONVERT=(‘D:\app\Administrator\oradata\CDB1\pdbseed’, ‘D:\app\Administrator\oradata\CDB1\pdb2’); 创建PDB3 ALTER SESSION SET PDB_FILE_NAME_CONVERT=‘D:\app\Administrator\
Oracle-PDB资源限制
sinat_36757755的博客
08-21 1912
在使用CDB容器数据库时，由于某些PDB出现高并发或者慢SQL，可能会出现PDB资源恶意争用的情况，最终导致一个PDB耗尽全部CDB资源。所以，为了避免PDB出现资源恶意争用，我们需要对PDB的资源，尤其是内存、CPU、IO、并发进行限制，以避免出现一个PDB耗尽全部CDB资源的情况。
sqlldr加载19c pdb最佳实践
fanzhuozhuo的博客
09-02 1852
oracle下载的软件包或者补丁文件校验背景sqlldr--数据文件sqlldr--控制文件PSU补丁校验命令 背景 sqlldr历史悠久，从oracle 7开始提供数据加载得功能。那么在高本版，甚至19c上面sqlldr还能否使用哪？ 某客户就有这种需求，经常需要把excel里面的数据导入到数据库表里面，那么sqlldr就成为不二之选。下面看下具体的使用和注意事项。 sqlldr–数据文件 一般会导出一个excel文件，或者csv文件，类似如下： 其中的内容如下： 为了后面的控制文件好些，一般插入个分
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值