oracle 9i(9.2.0.7.0)
oracle 10G(10.2.0.4.0) 64bit
oracle 11G(11.2.0.1.0) 64bit
最近需要将测试数据从O10g导入O9i数据库,用于测试O11g的JDBC(ojdbc14-10.2.0.4.jar)是否能工作于O9i, 因为高版本data dump不能被低版本exp/imp读取，我们使用低版本exp导出高版本数据，再用低版本imp导入O9i数据库. 过程出现下列两个错误,按照下列steps fixed：
exp时报以下错1
EXP-00003: no storage definition found for segment(14, 2131)
. . exporting table SYSTEM_MISC_CTRL_SETTING 1 rows exported
. . exporting table SYSTEM_PREFERENCE
EXP-00003: no storage definition found for segment(14, 2131)
. . exporting table SYSTEM_TRANSACTION_BOUNDARY 0 rows exported
使用旧版本的exp从9205(或以上)版本导出有lob对象的表时会出现 "EXP-00003: no storage
definition found for segment ....."的错误, 这是Oracle的一个Bug,
对于这个bug,可以作以下处理来成功导出数据.
在目标数据库的sys用户下运行(sqlplus / as sysdba):
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/
进行export操作, exp完成后, 最好将视图还原(Metalink建议).
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
/
EXU9TNE视图初始由 $ORACLE_HOME/rdbms/admin/catexp.sql 脚本创建.
fyi, http://www.51testing.com/?uid-33873-action-viewspace-itemid-216424
exp时报以下错2：
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 37002 encountered
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 473
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
以上是碰到的问题，了解到服务器是10G版本(10.2.0.3.0)，客户端是9I版本(9.2.0.1.0)
解决办法：关闭OLAP
1) Relink Oracle with the OLAP option turned off (olap_off make target) .
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk olap_off
2) Remove the relevant entries from exppkgact$:
SQL> select * FROM sys.exppkgact$ WHERE package='DBMS_AW_EXP';
PACKAGE SCHEMA CLASS LEVEL#
------------------------------ ------------------------------ ---------- ----------
DBMS_AW_EXP SYS 2 1000
DBMS_AW_EXP SYS 4 1000
DELETE FROM sys.exppkgact$ WHERE package='DBMS_AW_EXP';
