當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2023/12/14 数据库 23 豆豆

生活随笔收集整理的這篇文章主要介紹了 OCA/OCP Oracle 数据库12c考试指南读书笔记：第17章: Moving and Re-organizing Data 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

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

總結

以上是生活随笔為你收集整理的OCA/OCP Oracle 数据库12c考试指南读书笔记：第17章: Moving and Re-organizing Data的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：跑鸭”微信小程序-一款基于校园跑步的社交
下一篇：性能测试，响应时间