一、問題描述與分析：

今天碰到個問題，需求是將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;

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 parameter ，開始本想著測試一下EXPDP/IMPDP跨版本的實驗，使用到version引數，後來讀到官方文件發現限制裡還有些內容未能夠搞得很清楚，但是也學習到11g new feature，關於securefile lob欄位的內容，so interesting.沒啥好說的，go on, hard work,hard learning, where there’s a will,there’s a way.

expdp/impdp 使用version引數跨版本資料遷移

相關文章