oracle中如何使用exp/imp導(dǎo)入11g數(shù)據(jù)到9i

這篇文章將為大家詳細(xì)講解有關(guān)oracle中如何使用exp/imp導(dǎo)入11g數(shù)據(jù)到9i，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

方法1:導(dǎo)出導(dǎo)入都使用11g客戶端

--11g客戶端導(dǎo)出

[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客戶端導(dǎo)入

[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

這個錯誤是版本不兼容導(dǎo)致:PLS-00302: component ‘SET_NO_OUTLINES’ must be declared

方法2:11g客戶端導(dǎo)出,9i客戶端導(dǎo)入

--11g客戶端導(dǎo)出

[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.

--傳輸?shù)?i

[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客戶端導(dǎo)入

[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客戶端導(dǎo)出,9i客戶端導(dǎo)入

--9i客戶端導(dǎo)出

[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客戶端導(dǎo)入

[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

--導(dǎo)入數(shù)據(jù)遇到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導(dǎo)出11g數(shù)據(jù)

[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導(dǎo)入數(shù)據(jù)

[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.

--至此導(dǎo)入成功,完成了11gr2數(shù)據(jù)導(dǎo)入到9ir2中

通過一系列的實驗證明,需要把11g的數(shù)據(jù)導(dǎo)入到9i中,需要使用9i的客戶端進(jìn)行,其中exu9defpswitches視圖需要重建,否則會出現(xiàn)setSegmentation fault異常,導(dǎo)致導(dǎo)入失敗.

關(guān)于“oracle中如何使用exp/imp導(dǎo)入11g數(shù)據(jù)到9i”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。