一般來說，從低版本資料庫EXP資料，匯入至高版本資料庫是沒什麼問題的，因為Oracle資料庫在開發設計的時候，考慮了同以前版本的相容。但是從高本資料庫EXP匯出資料，匯入至低版本資料庫，經常會有各種各樣的問題。

在，在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以透過下面的方法來解決：
1）、在高版本資料庫上執行底版本的catexp.sql；11G的庫裡執行10G資料庫軟體下ORACLE_HOME/rdbms/admin/catexp.sql
2）、使用低版本的EXP來匯出高版本的資料；
3）、使用低版本的IMP將資料庫匯入到低版本資料庫中；
4）、在高版本資料庫上重新執行高版本的catexp.sql指令碼。

而大家目前用的最多的版本是Oracle 10G和11G，以前積累的經驗是否還適用，我們在此以實驗說明。

首先說明實驗環境，筆者的實驗環境是一個是10.0.2.0.4，作為匯入資料的目標庫

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

一個是11.2.0.3，作為實驗資料匯出的源庫

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

1. 首先在源庫上面建立實驗物件

SQL> create tablespace test datafile '+zhuxgasm' size 50M;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant dba,connect,resource to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table t1 as select * from dba_objects where rownum<=60;

Table created.

在這一步我們建立了實驗使用者test，並且建立了一個表T2，往裡面插入了60行的記錄。

2.在高版本資料庫中生成匯出檔案，驗證直接從高版本資料生成DMP檔案，匯入低版本資料庫的效果。

[oracle@zhuxg ~]$ exp system/kingstar file=/home/oracle/test01.dmp wner=test;

Export: Release 11.2.0.3.0 - Production on Sat May 11 12:00:09 2013

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table T1 60 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@zhuxg ~]$ ls

Desktop test01.dbf test01.dmp tts_test.dmp

從高版本資料庫生成了一個DMP檔案test01.dmp

3. 將dmp檔案從高版本資料庫機器複製至低版本資料庫機器

[oracle@zhuxg ~]$ scp test01.dmp 192.168.192.8:/home/oracle/
The authenticity of host '192.168.192.8 (192.168.192.8)' can't be established.
RSA key fingerprint is 44:bc:69:cd:9d:2b:b3:97:c7:65:55:4f:f3:5b:97:3c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.192.8' (RSA) to the list of known hosts.
oracle@192.168.192.8's password:
Permission denied, please try again.
oracle@192.168.192.8's password:
test01.dmp 100% 16KB 16.0KB/s 00:00
4. 將資料匯入至低版本資料庫
[oracle@book ~]$ imp system/kingstar file=test01.dmp fromuser=test touser=test

Import: Release 10.2.0.4.0 - Production on Sat Feb 9 05:05:03 2013

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

IMP-00000: Import terminated unsuccessfully

此路不通，直接從高版本資料庫匯出DMP檔案，往低版本資料庫插入，跟我們預想的一樣，不行。

接下來，我們按照以前大家的經驗做法，複製低版本$ORACLE_HOME/rdbms/admin下的catexp.sql到高版本客戶端（服務端），用sysdba許可權執行，然後用EXP工具匯出DMP檔案，在低版本資料庫客戶端（服務端）匯入。

5. 複製10G $ORACLE_HOME/rdbms/admin下的catexp.sql至11G機器，並且用sysdba許可權執行

[oracle@book admin]$ scp catexp.sql 192.168.192.2:/home/oracle/
oracle@192.168.192.2's password:
catexp.sql                                                                                                                                                   100% 381KB 381.0KB/s   00:00
[oracle@zhuxg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 11 12:31:25 2013

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @ /home/oracle/catexp.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following PL/SQL block will cause an ORA-20000 error and
DOC>    terminate the current SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.

CREATE ROLE exp_full_database
            *
ERROR at line 1:
ORA-01921: role name 'EXP_FULL_DATABASE' conflicts with another user or role
name

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

。。。。。。

更新的結果很長，這裡只擷取了一小部分用於展示。

6. 再次從11資料庫匯出dmp檔案，並傳輸至10G資料庫伺服器

[oracle@zhuxg ~]$ exp system/kingstar file=/home/oracle/test02.dmp wner=test;

Export: Release 11.2.0.3.0 - Production on Sat May 11 12:36:35 2013

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully

這裡匯出就有問題了。說明這個方法現在已經不可行了，那麼還有沒有其他方法呢？

我們重新執行高版本資料庫的catexp.sql，使高版本資料其恢復回原來的模樣。

SQL> @?/rdbms/admin/catexp.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following PL/SQL block will cause an ORA-20000 error and
DOC>    terminate the current SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.

Role created.

Grant succeeded.

。。。。。。。。。後面反饋資訊省略。

其他的方法是什麼呢，就是在低版本的客戶端（服務端）直接用網路服務連線高版本資料庫，然後用EXP工具匯出DMP檔案。如下：

7. 用低版本的聯通高版本的網路服務，然後用EXP匯出。

[oracle@book admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB77 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 112.65.228.77)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sgmparts)
    )
)

DB11G =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.192.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ASMDB)
    )
)

[oracle@book ~]$ exp system/kingstar@DB11G file=/home/oracle/test04.dmp wner=test;

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 06:11:13 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table T1 60 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

8. 在低版本資料庫中匯入

[oracle@book admin]$ imp test/test file=/home/oracle/test03.dmp full=y

Import: Release 10.2.0.4.0 - Production on Sat Feb 9 06:15:12 2013

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYSTEM's objects into TEST
. . importing table "T1" 60 rows imported

Import terminated successfully without warnings.

SQL> select count(*) from test.t1;

COUNT(*)
----------
60

匯入成功。

用EXP/IMP從高版本資料庫匯出至低版本資料庫匯入實驗