一般来说，从低版本数据库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
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)
)
)
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=yImport: 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
导入成功。
