How to import 11g data to 9i using exp/imp in oracle 02/23 Update SLTechnology News&Howtos

How to import 11g data to 9i using exp/imp in oracle

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to import 11g data to 9i using exp/imp in oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Method 1: export and import all use 11g client

-- 11g client export

[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

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 client import

[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

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

This error is caused by version incompatibility: PLS-00302: component 'SET_NO_OUTLINES' must be declared

Method 2Rom 11g client export, 9i client import

-- 11g client export

[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

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.

-- transfer to 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 client Import

[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

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

-- versions are not compatible (higher versions of dump files are not recognized by lower versions)

Method 3PUR 9i client export, 9i client import

-- 9i client export

[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

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 client Import

[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

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

-- Import data encountered setSegmentation fault abnormal termination

Resolve setSegmentation fault abnormal termination

-- modify exu9defpswitches view

[oracle@xifenfei ~] $sqlplus / as sysdba

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

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 exports 11g data

[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

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 Import data

[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

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.

-- at this point, the import is successful, and the 11gr2 data has been imported into 9ir2.

Through a series of experiments, it is proved that 11g of data needs to be imported into 9i, and the client of 9i needs to be used, in which the exu9defpswitches view needs to be rebuilt, otherwise there will be setSegmentation fault exceptions, resulting in import failure.

This is the end of this article on "how to import 11g data to 9i using exp/imp in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.