从10g开始,oracle提供了新的导出工具,expdp/impdp。又expdp导出的dump文件只能被impdp读取。又exp导出的dump文件不能被impdp读取。
查找导出工具版本
> exp HELP=Y
Export: Release 8.1.7.4.1 - ... (other output)
> imp HELP=Y
Import: Release 9.2.0.8.0 - ... (other output)
> expdp HELP=Y
Export: Release 10.1.0.5.0 - ... (other output)
> impdp HELP=Y
Import: Release 10.2.0.3.0 - ... (other output)
查找数据库版本
SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
查找dump文件版本
使用DBMS_DATAPUMP.GET_DUMPFILE_INFO存过
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_f.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: ftest.dmp
Directory: dpdir
Disk Path: /bugmnt1/em/celclnx14/SR3.7997287101/user/expdp
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.02.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Tue Jun 06 11:04:57 2017
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: db4
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYSTEM"."SYS_EXPORT_FULL_01"
...GUID (unique job identifier)..: 51470FA2BE241FE4E0539395400A4A02
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 23
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
数据泵工具和目标库版本的关系
可用看出,数据泵客户端低版本到高版本目标库都是支持的,到低版本目标库都不支持
版本间迁移到总结:
1.数据泵工具又低版本到高版本可行,但是不建议这样做
2.不支持高版本数据泵工具到低版本目标库
3.数据泵版本和数据库版本相同,可以使用 VERSION参数设置设置为较低版本 ,导入到高版本到数据库中。
4.如果源库是只读库,因为不能创建master表,所以即使是expdp导出也会失败。可用把只读库当作dblink库,然后在local库中创建master表,就可以正常导出。
5.如果想高版本导入到低版本,需要在expdp时使用version设置为目标库版本,然后再导入到目标库中
6.version的最低值为9.2。也就是说impdp最低能导入数据库的版本为9.2
7.当version参数高于dumpfile版本,数据泵的一些特性可能会不可用
参考文档:
Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (文档 ID 553337.1)
Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (文档 ID 132904.1)
查找导出工具版本
> exp HELP=Y
Export: Release 8.1.7.4.1 - ... (other output)
> imp HELP=Y
Import: Release 9.2.0.8.0 - ... (other output)
> expdp HELP=Y
Export: Release 10.1.0.5.0 - ... (other output)
> impdp HELP=Y
Import: Release 10.2.0.3.0 - ... (other output)
查找数据库版本
SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
查找dump文件版本
使用DBMS_DATAPUMP.GET_DUMPFILE_INFO存过
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_f.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: ftest.dmp
Directory: dpdir
Disk Path: /bugmnt1/em/celclnx14/SR3.7997287101/user/expdp
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.02.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Tue Jun 06 11:04:57 2017
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: db4
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYSTEM"."SYS_EXPORT_FULL_01"
...GUID (unique job identifier)..: 51470FA2BE241FE4E0539395400A4A02
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 23
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
![](http://img.blog.itpub.net/blog/attachment/201707/17/31461640_1500275033E9mB.png?x-oss-process=style/bb)
数据泵工具和目标库版本的关系
可用看出,数据泵客户端低版本到高版本目标库都是支持的,到低版本目标库都不支持
版本间迁移到总结:
1.数据泵工具又低版本到高版本可行,但是不建议这样做
2.不支持高版本数据泵工具到低版本目标库
3.数据泵版本和数据库版本相同,可以使用 VERSION参数设置设置为较低版本 ,导入到高版本到数据库中。
4.如果源库是只读库,因为不能创建master表,所以即使是expdp导出也会失败。可用把只读库当作dblink库,然后在local库中创建master表,就可以正常导出。
5.如果想高版本导入到低版本,需要在expdp时使用version设置为目标库版本,然后再导入到目标库中
6.version的最低值为9.2。也就是说impdp最低能导入数据库的版本为9.2
7.当version参数高于dumpfile版本,数据泵的一些特性可能会不可用
参考文档:
Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (文档 ID 553337.1)
Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (文档 ID 132904.1)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31461640/viewspace-2142164/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31461640/viewspace-2142164/