189 8069 5689
这篇文章将为大家详细讲解有关oracle中如何使用exp/imp导入11g数据到9i，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
我们提供的服务有：成都网站制作、成都网站设计、微信公众号开发、网站优化、网站认证、乌当ssl等。为1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的乌当网站制作公司
方法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”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。