expdp/impdp如何使用version參數跨版本數據遷移

166次閱讀

共計 16305 個字符，預計需要花費 41 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 expdp/impdp 如何使用 version 參數跨版本數據遷移，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

一、問題描述與分析：

碰到個問題，需求是將 Oracle 11g 版本的數據用 expdp 數據泵導出，再把數據導入至 Oracle 10 版本中，是否可以？一個不錯的問題，沒做過肯定不能亂說啦。首先我們來猜一下，我認為低版本導入高版本是可以的，高版本導入低版本就不一定了，但是我們想想 oracle 公司會讓 11g 的庫的內容無法導入到低版本嗎？答案在實驗中，接下來我們就立即做個這個實驗，進行驗證。

二、實驗

低版本— 高版本環境: 低版本 10g(10.2.0.1.0) 高版本 11g(11.2.0.1.0) 猜測：可以結論：可以

（1）10g 環境中創建測試用戶 sam，并賦予權限

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jan 5 19:06:25 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SYS@OCM10G select username from dba_users where username= SAM

no rows selected

SYS@OCM10G create user sam identified by sam;

User created.

SYS@OCM10G grant connect,resource to sam;

Grant succeeded.

（2）創建測試表 test 及數據

SYS@OCM10G conn sam/sam

Connected.

SAM@OCM10G create table test (id int,name varchar2(10));

Table created.

SAM@OCM10G insert into test values (1, sam

1 row created.

SAM@OCM10G commit;

Commit complete.

SAM@OCM10G select * from test;

ID NAME

———- ———-

1 sam

（3）創建導出數據時用到的 directory 并賦（讀 \ 寫）權限

SAM@OCM10G conn / as sysdba

Connected.

SYS@OCM10G create directory test as /home/oracle

Directory created.

SYS@OCM10G grant write,read on directory test to sam;

Grant succeeded.

（4）expdp 導出 10g 數據庫中 sam 用戶數據

[oracle@test ~]$ ls -l test.dmp

ls: cannot access test.dmp: No such file or directory

[oracle@test ~]$ expdp sam/sam@ocm10g directory=test dumpfile=test.dmp

Export: Release 10.2.0.1.0 – 64bit Production on Friday, 08 January, 2016 10:32:37

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting SAM . SYS_EXPORT_SCHEMA_01 : sam/********@ocm10g directory=test dumpfile=test.dmp

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported SAM . TEST 5.234 KB 1 rows

Master table SAM . SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded

******************************************************************************

Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/test.dmp

Job SAM . SYS_EXPORT_SCHEMA_01 successfully completed at 10:32:44

[oracle@test ~]$ ls -l test.dmp

-rw-r—– 1 oracle oinstall 143360 Jan 8 10:32 test.dmp

（5）創建 11g 環境，導入數據時用到的 directory，創建 SAM 用戶

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 10:34:48 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@OCM11G select username from dba_users where username= SAM

no rows selected

SYS@OCM11G create user sam identified by sam;

User created.

SYS@OCM11G grant connect,resource to sam;

Grant succeeded.

（6）將 test.dmp 文件導入 11g 數據庫

[oracle@test ~]$ impdp system/oracle@ocm11g directory=test dumpfile=test.dmp

Import: Release 11.2.0.1.0 – Production on Fri Jan 8 11:03:37 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table SYSTEM . SYS_IMPORT_FULL_01 successfully loaded/unloaded

Starting SYSTEM . SYS_IMPORT_FULL_01 : system/********@ocm11g directory=test dumpfile=test.dmp

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 SAM . TEST 5.234 KB 1 rows

Job SYSTEM . SYS_IMPORT_FULL_01 successfully completed at 11:03:39

（7）檢查用戶及導入數據

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:03:54 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@OCM11G

SYS@OCM11G conn sam/sam

Connected.

SAM@OCM11G select * from test;

ID NAME

———- ———-

1 sam

高版本— 低版本環境: 高版本 11g(11.2.0.1.0) 低版本 10g(10.2.0.1.0) 猜測：不成結論：可以，要使用 version 參數

（1）10g 庫中創建測試用戶 suzzy，并賦予權限

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:14:37 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@OCM11G select username from dba_users where username= SUZZY

no rows selected

SYS@OCM11G create user suzzy identified by suzzy;

User created.

SYS@OCM11G grant connect,resource to suzzy;

Grant succeeded.

（2）創建測試表 test1 及數據

SYS@OCM11G conn suzzy/suzzy

Connected.

SUZZY@OCM11G create table test1 (id int,name varchar2(10));

Table created.

SUZZY@OCM11G insert into test1 values (1, suzzy

1 row created.

SUZZY@OCM11G commit;

Commit complete.

SUZZY@OCM11G select * from test1;

ID NAME

———- ———-

1 suzzy

（3）給用戶 suzzy 賦予對導出目錄 test 的讀寫權限

SYS@OCM11G grant write,read on directory test to suzzy;

Grant succeeded.

（4）導出 11g 庫中 suzzy 用戶及數據

[oracle@test ~]$ expdp suzzy/suzzy@ocm11g directory=test dumpfile=test1.dmp

Export: Release 11.2.0.1.0 – Production on Fri Jan 8 11:29:14 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting SUZZY . SYS_EXPORT_SCHEMA_01 : suzzy/********@ocm11g directory=test dumpfile=test1.dmp

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported SUZZY . TEST1 5.429 KB 1 rows

Master table SUZZY . SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded

******************************************************************************

Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/test1.dmp

Job SUZZY . SYS_EXPORT_SCHEMA_01 successfully completed at 11:29:29

（5）創建 10g 數據庫中用戶并賦予權限，包括導入時用到的 directory

SYS@OCM10G select username from dba_users where username= SUZZY

no rows selected

SYS@OCM10G create user suzzy identified by suzzy;

User created.

SYS@OCM10G grant connect,resource to suzzy;

Grant succeeded.

SYS@OCM10G grant read,write on directory test to suzzy;

Grant succeeded.

（6）將 test1.dmp 文件導入 10g 數據庫，此時會報錯，原因是由于版本不符合要求，此時需要使用 expdp 中 version 參數，參數介紹請看文章（三）

[oracle@test ~]$ impdp suzzy/suzzy@ocm10g directory=test dumpfile=test1.dmp

Import: Release 10.2.0.1.0 – 64bit Production on Friday, 08 January, 2016 11:35:06

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 3.1 in dump file /home/oracle/test1.dmp

翻譯：

ORA-39001: 參數值無效

ORA-39000: 轉儲文件說明錯誤

ORA-39142: 版本號 3.1 (在轉儲文件 /home/oracle/test1.dmp 中) 不兼容

（7）增加 version 參數，重新從 11g 庫中導出 dump 文件

[oracle@test ~]$ expdp suzzy/suzzy@ocm11g directory=test dumpfile=test2.dmp version=10.2.0.1.0

Export: Release 11.2.0.1.0 – Production on Fri Jan 8 14:01:34 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting SUZZY . SYS_EXPORT_SCHEMA_01 : suzzy/********@ocm11g directory=test dumpfile=test2.dmp version=10.2.0.1.0

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported SUZZY . TEST1 5.304 KB 1 rows

Master table SUZZY . SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded

******************************************************************************

Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/test2.dmp

Job SUZZY . SYS_EXPORT_SCHEMA_01 successfully completed at 14:01:50

（8）將加參數 version 導出后的 test2.dmp 文件導入 10g 數據庫

[oracle@test ~]$ impdp suzzy/suzzy@ocm10g directory=test dumpfile=test2.dmp

Import: Release 10.2.0.1.0 – 64bit Production on Friday, 08 January, 2016 14:03:47

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table SUZZY . SYS_IMPORT_FULL_01 successfully loaded/unloaded

Starting SUZZY . SYS_IMPORT_FULL_01 : suzzy/********@ocm10g directory=test dumpfile=test2.dmp

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 SUZZY . TEST1 5.304 KB 1 rows

Job SUZZY . SYS_IMPORT_FULL_01 successfully completed at 14:03:48

（9）驗證用戶及數據

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jan 8 14:04:27 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SYS@OCM10G conn suzzy/suzzy

Connected.

SUZZY@OCM10G select * from test1;

ID NAME

———- ———-

1 suzzy

三、version 參數說明

（1）官方文檔：

VERSION

Default: COMPATIBLE

Purpose

Specifies the version of database objects to be exported (that is, only database objects and attributes that are compatible with the specified release will be exported). This can be used to create a dump file set that is compatible with a previous release of Oracle Database. Note that this does not mean that Data Pump Export can be used with releases of Oracle Database prior to Oracle Database 10g release 1 (10.1). Data Pump Export only works with Oracle Database 10g release 1 (10.1) or later. The VERSION parameter simply allows you to identify the version of the objects being exported.

Syntax and Description

VERSION=[COMPATIBLE | LATEST | version_string]

The legal values for the VERSION parameter are as follows:

COMPATIBLE – This is the default value. The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher.

LATEST – The version of the metadata corresponds to the database release.

version_string – A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2.

Database objects or attributes that are incompatible with the specified release will not be exported. For example, tables containing new datatypes that are not supported in the specified release will not be exported.

Restrictions

Exporting a table with archived LOBs to a database release earlier than 11.2 is not allowed.

If the Data Pump Export VERSION parameter is specified along with the TRANSPORT_TABLESPACES parameter, then the value must be equal to or greater than the Oracle Database COMPATIBLE initialization parameter.

（2）說明

根據官方文檔，我們可以看到 version 參數有 3 個選項可以選，分別為 compatible、latest、version_string，默認選項是 compatible，這個選項是導出的元數據與數據庫的版本兼容性級別一致，例如我導出的數據庫兼容性是 11.2.0.0.0 那么導出時就是 11.2.0.0.0，這個很好理解。latest 選項是最高版本與數據庫版本保持一致，這個基本很少用到。version_string 是指定具體數據庫版本的字符串，這個很常用，在清楚自己數據庫版本時，可以直接指定導出兼容版本，注意在 11g 的版本中，該參數指定版本不能小于 9.2。

再有就是數據庫對象與屬性也要對該版本支持，否則不會被導出，例如，包含新數據類型的表不支持指定的版本不會被導出。

限制：

低于 11.2 版本，不允許導出帶有 archived LOBs 表（通過字面上未能太理解，在群里做了些咨詢，也沒有最終的結果，不過還是有些收獲，11g 中導出的包含 securefile lob 的表，導入到 10g 中會丟失到該特性，見四）

如果數據泵 version 參數隨著 TRANSPORT_TABLESPACES 指定參數, 那么 version 值必須等于或大于 Oracle 數據庫初始化參數兼容。

四、關于 securefile lob 字段導出導入測試

創建測試表

11g 環境:

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 18:58:37 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@OCM11G show parameter db_securefile

NAME TYPE VALUE

———————————— ———– ——————————

db_securefile string PERMITTED

SYS@OCM11G conn sam/sam

Connected.

SAM@OCM11G create table t1(a clob) lob (a) store as securefile (compress low cache nologging);

Table created.

SAM@OCM11G insert into t1 select rpad(a ,4000, *) str from dual connect by rownum

10 rows created.

SAM@OCM11G commit;

Commit complete.

導出數據

[oracle@test ~]$ expdp sam/sam@ocm11g directory=test dumpfile=test_lob8.dmp version=10.2.0.1.0

Export: Release 11.2.0.1.0 – Production on Wed Jan 13 19:10:42 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting SAM . SYS_EXPORT_SCHEMA_01 : sam/********@ocm11g directory=test dumpfile=test_lob8.dmp version=10.2.0.1.0

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 576 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported SAM . T1 83.51 KB 11 rows

. . exported SAM . T_CLOB 83.54 KB 10 rows

. . exported SAM . TEST_LOB 5.382 KB 1 rows

. . exported SAM . TEST 5.296 KB 1 rows

Master table SAM . SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded

******************************************************************************

Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/test_lob8.dmp

Job SAM . SYS_EXPORT_SCHEMA_01 successfully completed at 19:10:58

導入 10g 環境

[oracle@test ~]$ impdp sam/sam@ocm10g directory=test dumpfile=test_lob8.dmp

Import: Release 10.2.0.1.0 – 64bit Production on Wednesday, 13 January, 2016 19:13:15

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table SAM . SYS_IMPORT_FULL_01 successfully loaded/unloaded

Starting SAM . SYS_IMPORT_FULL_01 : sam/********@ocm10g directory=test dumpfile=test_lob8.dmp

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 SAM . T1 83.51 KB 11 rows

. . imported SAM . T_CLOB 83.54 KB 10 rows

. . imported SAM . TEST_LOB 5.382 KB 1 rows

. . imported SAM . TEST 5.296 KB 1 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job SAM . SYS_IMPORT_FULL_01 successfully completed at 19:13:16

對比 10g，11g 庫中 t1 表結構

（11g）

SYS@OCM11G select dbms_metadata.get_ddl(TABLE , T1 , SAM) from dual;

DBMS_METADATA.GET_DDL(TABLE , T1 , SAM)

——————————————————————————–

CREATE TABLE SAM . T1

( A CLOB

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE USERS

LOB (A) STORE AS SECUREFILE (

TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 8192

CACHE NOLOGGING COMPRESS LOW KEEP_DUPLICATES

STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

))

（10g）

SYS@OCM10G select dbms_metadata.get_ddl(TABLE , T1 , SAM) from dual;

DBMS_METADATA.GET_DDL(TABLE , T1 , SAM)

——————————————————————————–

CREATE TABLE SAM . T1

( A CLOB

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE USERS

LOB (A) STORE AS (

TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0

CACHE

STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

以上是“expdp/impdp 如何使用 version 參數跨版本數據遷移”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！

正文完

發表至：數據庫

2023-07-24

轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。

mysql57的打開方法

mysql中新建表和理清表之間有什么聯系

windows上mysql如何進行主從數據庫搭建及問題是什么

從SQL到NoSQL7種比較查詢語言的指標分別是什么

Oracle12 RMAN新特性Recover Table有什么作用

久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

expdp/impdp如何使用version參數跨版本數據遷移