一、問題描述與分析： 今天碰到個問題，需求是將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 sysdbaSQL*Plus: Release 10.2.0.1.0 - PRoduction on Tue Jan 5 19:06:25 2016Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSYS@OCM10G >select username from dba_users where username='SAM';no rows selectedSYS@OCM10G >create user sam identified by sam;User created.SYS@OCM10G >grant connect,resource to sam;Grant succeeded.（2）創建測試表test及數據SYS@OCM10G >conn sam/samConnected.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 sysdbaConnected.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.dmpls: 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:37Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "SAM"."TEST" 5.234 KB 1 rowsMaster table "SAM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/test.dmpJob "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 sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 10:34:48 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real application Testing optionsSYS@OCM11G >select username from dba_users where username='SAM';no rows selectedSYS@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 2016Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@ocm11g directory=test dumpfile=test.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SAM"."TEST" 5.234 KB 1 rowsJob "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:03:39（7）檢查用戶及導入數據[oracle@test ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:03:54 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@OCM11G >SYS@OCM11G >conn sam/samConnected.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 sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 8 11:14:37 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@OCM11G >select username from dba_users where username='SUZZY';no rows selectedSYS@OCM11G >create user suzzy identified by suzzy;User created.SYS@OCM11G >grant connect,resource to suzzy;Grant succeeded.（2）創建測試表test1及數據SYS@OCM11G >conn suzzy/suzzyConnected.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.dmpExport: Release 11.2.0.1.0 - Production on Fri Jan 8 11:29:14 2016Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "SUZZY"."TEST1" 5.429 KB 1 rowsMaster table "SUZZY"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is: /home/oracle/test1.dmpJob "SUZZY"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:29:29（5）創建10g數據庫中用戶并賦予權限，包括導入時用到的directorySYS@OCM10G >select username from dba_users where username='SUZZY';no rows selectedSYS@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:06Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-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 2016Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "SUZZY"."TEST1" 5.304 KB 1 rowsMaster table "SUZZY"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SUZZY.SYS_EXPORT_SCHEMA_01 is: /home/oracle/test2.dmpJob "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:47Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsMaster table "SUZZY"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SUZZY"."SYS_IMPORT_FULL_01": suzzy/********@ocm10g directory=test dumpfile=test2.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SUZZY"."TEST1" 5.304 KB 1 rowsJob "SUZZY"."SYS_IMPORT_FULL_01" successfully completed at 14:03:48（9）驗證用戶及數據[oracle@test ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 8 14:04:27 2016Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSYS@OCM10G >conn suzzy/suzzyConnected.SUZZY@OCM10G >select * from test1; ID NAME---------- ---------- 1 suzzy三、version參數說明（1）官方文檔：VERSIONDefault: COMPATIBLEPurposeSpecifies 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 DescriptionVERSION=[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.RestrictionsExporting 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 sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 18:58:37 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@OCM11G >show parameter db_securefileNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_securefile string PERMITTEDSYS@OCM11G >conn sam/samConnected.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.0Export: Release 11.2.0.1.0 - Production on Wed Jan 13 19:10:42 2016Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SAM"."SYS_EXPORT_SCHEMA_01": sam/********@ocm11g directory=test dumpfile=test_lob8.dmp version=10.2.0.1.0Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 576 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing 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 rowsMaster table "SAM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SAM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/test_lob8.dmpJob "SAM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:10:58導入10g環境[oracle@test ~]$ impdp sam/sam@ocm10g directory=test dumpfile=test_lob8.dmpImport: Release 10.2.0.1.0 - 64bit Production on Wednesday, 13 January, 2016 19:13:15Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsMaster table "SAM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SAM"."SYS_IMPORT_FULL_01": sam/********@ocm10g directory=test dumpfile=test_lob8.dmpProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing 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 rowsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "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 DEFAULT 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.
