最近很多小伙伴都在问OracleDataPump工具系列：如何重新安装DataPumpEXPDP/IMPDP和oracle怎么重新安装这两个问题，那么本篇文章就来给大家详细解答一下，同时本文还将给你拓
最近很多小伙伴都在问Oracle Data Pump 工具系列：如何重新安装 DataPump EXPDP/IMPDP和oracle怎么重新安装这两个问题，那么本篇文章就来给大家详细解答一下，同时本文还将给你拓展DATAPUMP PERFORMANCE EXPDP IS VERY SLOW 10.2.0.4 TO 11.2.0.2、Exp/Imp, Expdp/Impdp、expdp impdp 数据库导入导出命令详解、expdp/impdp 使用 version 参数跨版本数据迁移等相关知识，下面开始了哦！
本文目录一览：- Oracle Data Pump 工具系列：如何重新安装 DataPump EXPDP/IMPDP（oracle怎么重新安装）
- DATAPUMP PERFORMANCE EXPDP IS VERY SLOW 10.2.0.4 TO 11.2.0.2
- Exp/Imp, Expdp/Impdp
- expdp impdp 数据库导入导出命令详解
- expdp/impdp 使用 version 参数跨版本数据迁移
Oracle Data Pump 工具系列：如何重新安装 DataPump EXPDP/IMPDP（oracle怎么重新安装）
如何重载 Datapump 实用工具 EXPDP/IMPDP 本文适用于
如何重载 Datapump 实用工具 EXPDP/IMPDP
本文适用于oracledatabase - enterprise edition - version 10.1.0.2 to 11.2.0.3
在很多不同的场景下都可能需要重载 DATAPUMP实用工具，比如数据库出现hang机、内部错误、数据字典不兼容等与DataPump 初始化阶段有关的所有问题。
在某些情况下，DataPump 实用工具可能会损坏，我们需要重建DataPump 实用工具来解决一些内部损坏。要执行此操作，可以按照下面给出的脚本来进行：
注意： 以 sysdba 用户执行如下脚本：
SQL> connect / as sysdba
- 对于 Oracle version 10.1 版本：
1. Catdp.sql 负责安装 DataPump 工具的所有组件，包括之前单独安装的 Metadata API。 默认情况下， catproc.sql 调用此脚本。
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql
2. dbmspump.sql 用于创建 DataPump 所用的 DBMS 过程。
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
- 对于 Oracle version 10.2 版本：
1. Catdph.sql 用于重新安装 DataPump 类型和视图
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
注意： 如果在安装了 XDB的情况下，则还需要运行 "catmetx.sql" 脚本。
可以使用如下脚本来验证是否已安装 XDB：
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12)version,status from dba_registry;
如果已安装XDB，则会按照如下示例输出
Oracle XML Database XDB -version- VALID
2. prvtdtde.plb 用于重新安装 tde_library 包
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb
3. Catdpb.sql 用于重新安装 DataPump 包
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql
4.Dbmspump.sql 用于重新安装 DBMS DataPump 对象
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
5. 如果需要可以执行如下脚本编译无效的对象
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql
- For Oracle version 11g:
1. Catproc.sql
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql
2. 如果需要可以执行如下脚本编译无效的对象
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql
http://blog.csdn.net/xiangsir/article/details/8806457
DATAPUMP PERFORMANCE EXPDP IS VERY SLOW 10.2.0.4 TO 11.2.0.2
APPLIES TO:
Oracle? Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
information in this document applies to any platform.
***Checked for relevance on 29-Feb-2016***
SYMPTOMS
Linux x86-64 running standard edition 11.2.0.2 with RAC reporting slow performance on expdp for object_grants.
CAUSE
Bug 9791589 DataPump export hang / uses excessive memory with many "WITH GRANT OPTION" grants - superseded (Note 9791589.8)
Large numbers of WITH GRANT OPTION object grants can cause DataPump to run slowly and run out of memory.
Rediscovery Notes: expdp (DataPump export) appears to hang. The hang occurs with INCLUDE=OBJECT_GRANT and does not occur with EXCLUDE=OBJECT_GRANT.
E.g.:
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-4030: out of process memory when trying to allocate 4032 bytes (kxs-heap-w,kghsseg : kokeismo)
ORA-06512: at "SYS.KUPW$WORKER",line 1714
ORA-06512: at line 2
Workaround:
Use EXCLUDE=OBJECT_GRANT
Note:
This fix causes the problem described in unpublished bug 10185319. This fix is superseded by that fix.
Both fixes,both 9791589 and 10185319 must be installed to address memory leak error.
Unpublished Bug 10185319 - EXPDP not changing to PUBLIC user correctly (Note 10185319.8)
With the fix for bug 9791589 present (11.2.0.2) EXPDP may not change user to PUBLIC correctly.
Note:
This fix is superseded by the fix for bug 10195109 Both fixes,both 9791589 and 10195109 must be installed to address memory leak error.
Bug 10195109 - ORA-4030 during datapump Metadata export (Note 10195109.8)
Memory growth may be seen datapump export,e.g.:
ORA-4030: out of process memory when trying to allocate 4032 bytes (kxs-heap-w,kghsseg : kokeismo)
ORA-6512: at "SYS.KUPW$WORKER",line 1714
ORA-6512: at line 2
All are kNown bugs which impact datapump export processing due to excess memory usage and slow performance on Metadata export.
The superseding Bug 10195109 is fixed in release 12c and patch set 11.2.0.3.
Please refer to
Note 10195109.8 - Bug 10195109 - ORA-4030 during datapump Metadata export
SOLUTION
To solve the issue,use any of below alternatives:
o Upgrade to 12c
- OR -
o Apply patch set 11.2.0.3 or higher
- OR -
o Apply interim Patch 10195109 if available for your platform and (PSU) patch level
To check for conflicting patches,please use the MOS Patch Planner Tool
Please refer to
Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
REFERENCES
BUG:10195109 - ORA-4030 DURING DATAPUMP MetaDATA EXPORTNOTE:10185319.8 - Bug 10185319 - EXPDP not changing to PUBLIC user correctly - supercedednOTE:10195109.8 - Bug 10195109 - ORA-4030 during datapump Metadata exportNOTE:1267951.1 - DataPump Import (IMPDP) is Very Slow at Object/System/Role Grants,Default RolesNOTE:9791589.8 - Bug 9791589 - Data Pump export hang / uses excessive memory with many "WITH GRANT OPTION" grants - superceded
Exp/Imp, Expdp/Impdp
Legacy EXPORT/IMPORT (exp/imp)What is now known as legacy export/import (exp/imp) was introduced in Oracle 7.1 and were called ''conventional path exports'' . This meant that the ''conventional path export used "SELECT * FROM OWNER.TABLE" to extract the data and push through various buffers and finally to the export file on the client side. It wasn''t until Oracle 7.3 that ''direct path exports'' were introduced.
- Exports broken up into 3 categories
- Incremental - will backup only the tables that have changed since the last incremental, cumulative or complete export.
- Cumulative - will backup only the tables that have changed since the last cumulative or complete export.
- Complete - will backup the entire database, with the exception of updates on tables where incremental and cumulative exports are tracked. The parameter "INCTYPE=COMPLETE" must be specified for those tables with updates and are tracked to be backed up.
- Export broken up into 3 modes
- Exporting table - export table or group of tables
- Exporting users - can backup all objects in a particular users schema including tables/data/grants/indexes
- Exporting full database - exports the entire database
- Import broken into the same 3 modes as export
- Importing table/s
- Importing user/s
- Importing full database
Data pump expdp/impdp utilities were first introduced in Oracle 10g (10.1). These new utilities were considered high performance replacements for the legacy export/import had been in use since Oracle 7. Some of the key advantages are: greater data/metadata filtering, handling of partitioned tables during import and support of the full range of data types.
- Expdp/Impdp provide same categories and modes as the legacy exp/imp utility
- Plus more
- Legacy exp/imp commands are transformed into new parameters
- Legacy Data pump
- user/s schema/s
- file dumpfile
- log logfile
- For full list of all of the new parameter mappings visit this link for dp_legacy support documentation for 11.2 which is latest. Oracle 11.2 DP_Legacy Parameter Mappings
- Expdp/Impdp offers ability to
- Stop/Kill/Attach to a datapump job
- Export/Import data in parallel
- Need to use the ''%U'' in your dumpfile name
- dumpfile=exp_test1_09202012_%U.dmp
- Expdp/Impdp major features
- Overall performance of datapump import is 15-45x faster than legacy import
- Network mode - allows import over dblink
- Restart - ability to stop and reattach to the job
- Include/Exclude - allow/disallow objects to be included/excluded during export/import
- Content - metadata_only, data_only or all
- Estimate - provides the estimated size of dumpfiles so space allocation can be made beforehand
- Compression - ability to compress your data up to 5x
- For a full review of the data pump performance and other features, please refer to the Oracle White Paper "Data Pump in Oracle 11gR2"
- Exp/Imp can be used from Oracle 7.1 on up to current release Oracle 11.2
- Expdp/Impdp can ONLY be used from Oracle 10.1 to current release Oracle 11.2
- Upgrading from a pre-10g Oracle database you must import your data using the current Oracle 11.2 legacy import utility as data pump was not available prior to Oracle 10.1
- Data pump exports present the data in an XML format
- Data pump will create the user as part of the import as previously with legacy import the dba would need to create the user beforehand and then recreate all of the necessary grants.
- With data pump you can stop and resume data pump jobs
- Data pump is a faster solution with better reliability and more automation.
expdp impdp 数据库导入导出命令详解
create directory dpdata1 as ''d:\test\dump'';
二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;
三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。
grant read,write on directory dpdata1 to scott;
四、导出数据
1)按用户导
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行进程 parallel
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名导
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查询条件导
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query=''WHERE deptno=20'';
5)按表空间导
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导整个数据库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
五、还原数据
1)导到指定用户下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2)改变表的 owner
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
3)导入表空间
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)导入数据库
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;
1. ATTACH
该选项用于在客户会话与已存在导出作用之间建立关联.语法如下
ATTACH=[schema_name.]job_name
Schema_name 用于指定方案名,job_name用于指定导出作业名.注意,如果使用ATTACH选项,在命令行除了连接字符串和ATTACH选项外,不能指定任何其他选项,示例如下 :
Expdp scott/tiger ATTACH=scott.export_job
2. CONTENT
该选项用于指定要导出的内容.默认值为 ALL
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
当设置CONTENT为ALL 时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump
CONTENT=METADATA_ONLY
3. DIRECTORY
指定转储文件和日志文件所在的目录
DIRECTORY=directory_object
Directory_object 用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS 目录
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump
建立目录 :
CREATE DIRECTORY dump as ‘d:dump’;
查询创建了那些子目录 :
SELECT * FROM dba_directories;
4. DUMPFILE
用于指定转储文件的名称,默认名称为 expdat.dmp
DUMPFILE=[directory_object:]file_name [,….]
Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象
Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp
5. ESTIMATE
指定估算被导出表所占用磁盘空间分方法.默认值是 BLOCKS
EXTIMATE={BLOCKS | STATISTICS}
设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为STATISTICS时,根据最近统计值估算对象占用空间
Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS
DIRECTORY=dump DUMPFILE=a.dump
6. EXTIMATE_ONLY
指定是否只估算导出作业所占用的磁盘空间,默认值为 N
EXTIMATE_ONLY={Y | N}
设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作 .
Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y
7. EXCLUDE
该选项用于指定执行操作时释放要排除对象类型或相关对象
EXCLUDE=object_type[:name_clause] [,….]
Object_type 用于指定要排除的对象类型,name_clause用于指定要排除的具体对象.EXCLUDE和INCLUDE不能同时使用
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW
8. FILESIZE
指定导出文件的最大尺寸,默认为0,(表示文件尺寸没有限制 )
9. FLASHBACK_SCN
指定导出特定SCN时刻的表数据
FLASHBACK_SCN=scn_value
Scn_value 用于标识SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同时使用
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp
FLASHBACK_SCN=358523
10. FLASHBACK_TIME
指定导出特定时间点的表数据
FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=
“TO_TIMESTAMP(’25-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)”
指定数据库模式导出,默认为 N
FULL={Y | N}
为Y时,标识执行数据库导出 .
12. HELP
指定是否显示EXPDP命令行选项的帮助信息,默认为 N
当设置为Y时,会显示导出选项的帮助信息 .
Expdp help=y
13. INCLUDE
指定导出时要包含的对象类型及相关对象
INCLUDE = object_type[:name_clause] [,… ]
14. JOB_NAME
指定要导出作用的名称,默认为 SYS_XXX
JOB_NAME=jobname_string
15. LOGFILE
指定导出日志文件文件的名称,默认名称为 export.log
LOGFILE=[directory_object:]file_name
Directory_object用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY的相应选项值 .
Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log
16. NETWORK_LINK
指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项 .
17. NOLOGFILE
该选项用于指定禁止生成导出日志文件,默认值为 N.
18. PARALLEL
指定执行导出操作的并行进程个数,默认值为 1
19. PARFILE
指定导出参数文件的名称
PARFILE=[directory_path] file_name
20. QUERY
用于指定过滤导出数据的where条件
QUERY=[schema.] [table_name:] query_clause
Schema 用于指定方案名,table_name用于指定表名,query_clause用于指定条件限制子句.QUERY选项不能与 CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES等选项同时使用 .
Expdp scott/tiger directory=dump dumpfiel=a.dmp
Tables=emp query=’WHERE deptno=20’
21. SCHEMAS
该方案用于指定执行方案模式导出,默认为当前用户方案 .
22. STATUS
指定显示导出作用进程的详细状态,默认值为 0
23. TABLES
指定表模式导出
TABLES=[schema_name.]table_name[:partition_name][,…]
Schema_name 用于指定方案名,table_name用于指定导出的表名,partition_name用于指定要导出的分区名 .
24. TABLESPACES
指定要导出表空间列表
25. TRANSPORT_FULL_CHECK
该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式,默认为 N.
当设置为Y时,导出作用会检查表空间直接的完整关联关系,如果表空间所在表空间或其索引所在的表空间只有一个表空间被搬移,将显示错误信息.当设置为N时, 导出作用只检查单端依赖,如果搬移索引所在表空间,但未搬移表所在表空间,将显示出错信息,如果搬移表所在表空间,未搬移索引所在表空间,则不会显示错误信息 .
26. TRANSPORT_TABLESPACES
指定执行表空间模式导出
27. VERSION
指定被导出对象的数据库版本,默认值为 COMPATIBLE.
VERSION={COMPATIBLE | LATEST | version_string}
为COMPATIBLE时,会根据初始化参数COMPATIBLE生成对象元数据;为LATEST时,会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.调用 EXPDP
使用EXPDP工具时,其转储文件只能被存放在DIRECTORY对象对应的OS目录中,而不能直接指定转储文件所在的OS目录.因此 ,
使用EXPDP工具时,必须首先建立DIRECTORY对象.并且需要为数据库用户授予使用DIRECTORY对象权限.
1.Data Pump 导出 expdp
例子：
sql>create directory dpdata1 as ''/u02/dpdata1'';
sql>grant read, write on directory dpdata1 to ananda;
$expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT
$expdp ananda/abc123 tables=CASES directory=DPDATA1
dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export
include/exclude 例子：
include=table:"in(''DB'',''TS'')"
或者 include=table:"like ''%E%''"
或者 include=function,package,procedure,table:"=''EMP''"
或者 exclude=SEQUENCE,TABLE:"IN (''EMP'',''DEPT'')"
2.Data Pump 导入 expdp
1) 从expdp中获取数据源 exp.dmp
2)复制某个数据库中的一个schema到另一个数据库中。
3) 在同一个数据库中把一个schema中所有的对象复制到另一个schema中。
例子：
1)impdp 的数据源是expdp 导出来的DMP文件
impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import
2) 复制某个数据库中的一个schema到另一个数据库中。
--1.newwork_link 为目标数据库建立的database_link，
( 用户test 需要 grant exp_full_database to TEST; )
create public database link TOLINK
connect to TEST identified by oracle
using ''(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.20.199)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)'';
--2.impdp在目标数据库服务器上执行 只能低版本向高版本 imp
impdp network_link=TOLINK schemas=test remap_schema=test:link2
3) 在同一个数据库中把一个schema中所有的对象复制到另一个schema中。
--1. 创建连接自己的 database link:
create public database link system_self connect to system identified by "system" using ''orcl'';
数据库链接已创建。
--2. 复制hr schema到 test schema:
impdp system/system network_link=system_self schemas=hr remap_schema=hr:test
expdp/impdp 使用 version 参数跨版本数据迁移
一、问题描述与分析：
今天碰到个问题，需求是将 Oracle 11g 版本的数据用 expdp 数据泵导出，再把数据导入至 Oracle 10 版本中，是否可以？一个不错的问题，没做过肯定不能乱说啦。首先我们来猜一下，我认为低版本导入高版本是可以的，高版本导入低版本就不一定了，但是我们想想 oracle 公司会让 11g 的库的内容无法导入到低版本吗？答案在实验中，接下来我们就立即做个这个实验，进行验证。
二、实验
1. 低版本 —> 高版本 环境：低版本 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
2. 高版本 —> 低版本 环境：高版本 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 字段导出导入测试
1. 创建测试表
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;
10 rows created.
SAM@OCM11G >commit;
Commit complete.
2. 导出数据
[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
3. 导入 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
4. 对比 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.
今天关于Oracle Data Pump 工具系列：如何重新安装 DataPump EXPDP/IMPDP和oracle怎么重新安装的介绍到此结束，谢谢您的阅读，有关DATAPUMP PERFORMANCE EXPDP IS VERY SLOW 10.2.0.4 TO 11.2.0.2、Exp/Imp, Expdp/Impdp、expdp impdp 数据库导入导出命令详解、expdp/impdp 使用 version 参数跨版本数据迁移等更多相关知识的信息可以在本站进行查询。
本文标签：
