共计 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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (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 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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (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 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (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 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (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 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
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 行业资讯频道！
