oracle中如何使用exp/imp导入11g数据到9i

共计 6005 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 oracle 中如何使用 exp/imp 导入 11g 数据到 9i，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

方法 1: 导出导入都使用 11g 客户端

–11g 客户端导出

[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp

log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 11.2.0.3.0 – Production on Fri May 18 18:15:18 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …

. . exporting table                     T_XIFENFEI          2 rows exported

Export terminated successfully without warnings.

–11g 客户端导入

[oracle@xifenfei ~]$ imp chf/xifenfei@ora9i file=/tmp/t_xifenfei_11g.dmp

log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Import: Release 11.2.0.3.0 – Production on Fri May 18 18:17:24 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

IMP-00058: ORACLE error 6550 encountered

ORA-06550: line 1, column 33:

PLS-00302: component SET_NO_OUTLINES must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

IMP-00000: Import terminated unsuccessfully

这个错误是版本不兼容导致:PLS-00302: component‘SET_NO_OUTLINES’must be declared

方法 2:11g 客户端导出,9i 客户端导入

–11g 客户端导出

[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp

log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 11.2.0.3.0 – Production on Fri May 18 18:15:18 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …

. . exporting table                     T_XIFENFEI          2 rows exported

Export terminated successfully without warnings.

– 传输到 9i

[oracle@xifenfei tmp]$ scp t_xifenfei.dmp 192.168.1.10:/tmp/

The authenticity of host 192.168.1.10 (192.168.1.10) can t be established.

RSA key fingerprint is 3d:0c:d1:4b:45:bd:a3:f5:25:eb:4d:52:d2:32:03:69.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 192.168.1.10 (RSA) to the list of known hosts.

oracle@192.168.1.10 s password:

t_xifenfei.dmp                          100%   56KB  56.0KB/s   00:00    

–9i 客户端导入

[oracle@xifenfei ~]$ imp chf/xifenfei file=/tmp/t_xifenfei.dmp tables=t_xifenfei

Import: Release 9.2.0.4.0 – Production on Thu May 24 23:32:18 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 – Production

IMP-00010: not a valid export file, header failed verification

IMP-00000: Import terminated unsuccessfully

– 版本不兼容 (高版本的 dump 文件低版本不能识别)

方法 3:9i 客户端导出,9i 客户端导入

–9i 客户端导出

[oracle@xifenfei ~]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp

log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 9.2.0.4.0 – Production on Thu May 24 23:37:20 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …

. . exporting table                     T_XIFENFEI          2 rows exported

Export terminated successfully without warnings.

–9i 客户端导入

[oracle@xifenfei log]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp log=/tmp/xifenfei.log full=y

Import: Release 9.2.0.4.0 – Production on Fri May 25 03:22:14 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 – Production

Export file created by EXPORT:V09.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character setSegmentation fault

– 导入数据遇到 setSegmentation fault 异常终止

解决 setSegmentation fault 异常终止

– 修改 exu9defpswitches 视图

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 18 22:29:00 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL CREATE OR REPLACE VIEW exu9defpswitches (

 2                  compflgs, nlslensem ) AS

 3          SELECT  a.value, b.value

 4          FROM    sys.v$parameter a, sys.v$parameter b

 5          WHERE   a.name = plsql_code_type AND

 6                  b.name = nls_length_semantics

View created.

–9i 导出 11g 数据

[oracle@xifenfei tmp]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp

log=/tmp/xifenfei.log tables=t_xifenfei

Export: Release 9.2.0.4.0 – Production on Fri May 25 04:08:32 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …

. . exporting table                     T_XIFENFEI          2 rows exported

Export terminated successfully without warnings.

–9i 导入数据

[oracle@xifenfei tmp]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp

log=/tmp/xifenfei.log tables=t_xifenfei

Import: Release 9.2.0.4.0 – Production on Fri May 25 04:08:53 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 – Production

Export file created by EXPORT:V09.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing CHF s objects into CHF

. . importing table                   T_XIFENFEI          2 rows imported

Import terminated successfully without warnings.

– 至此导入成功, 完成了 11gr2 数据导入到 9ir2 中

通过一系列的实验证明, 需要把 11g 的数据导入到 9i 中, 需要使用 9i 的客户端进行, 其中 exu9defpswitches 视图需要重建, 否则会出现 setSegmentation fault 异常, 导致导入失败.

关于“oracle 中如何使用 exp/imp 导入 11g 数据到 9i”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。