使用9I EXP迁移11G数据库迁移到9I报错

qq7343696

环境如下：

目标端：9.2.0.4 （LINUX X84_64）    源端:11.2.0.3(LINUX X86_64)

要求把11G其中一个用户的所有表与数据迁移到9I数据库中。


下面测试报错：

[oracle9i@rhel4 admin]$ exp scott/oracle@orcl11g file='/tmp/scott_test_exp.dmp' tables='test_exp';

Export: Release 9.2.0.4.0 - Production on Fri Mar 22 06:12:43 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


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

About to export specified tables via Conventional Path ...
. . exporting table                       TEST_EXP      74622 rows exported
Export terminated successfully without warnings.

[oracle9i@rhel4 admin]$ imp scott/oracle file='/tmp/scott_test_exp.dmp' tables='test_exp'

Import: Release 9.2.0.4.0 - Production on Fri Mar 22 06:12:53 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
IMP-00003: ORACLE error 96 encountered
ORA-00096: invalid value  for parameter plsql_compiler_flags, must be from among NON_DEBUG, DEBUG, INTERPRETED, NATIVE
IMP-00000: Import terminated unsuccessfully

导入的时候报 plsql_compiler_flags无效的参数。

目标端此参数为

SQL> show parameter plsql_compiler_flags

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_compiler_flags                 string      INTERPRETED

源端已经没有此参数了

qq7343696

问题已经解决：

[oracle11g@rhel4 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 22 07:27:50 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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'
  7  ;

View created.


[oracle9i@rhel4 admin]$ exp scott/ORACLE@orcl11g file='/tmp/scott_test_exp.dmp' tables='test_exp';

Export: Release 9.2.0.4.0 - Production on Fri Mar 22 07:25:43 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


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

About to export specified tables via Conventional Path ...
. . exporting table                       TEST_EXP      74622 rows exported
Export terminated successfully without warnings.
[oracle9i@rhel4 admin]$ imp scott/oracle file='/tmp/scott_test_exp.dmp' tables='test_exp'

Import: Release 9.2.0.4.0 - Production on Fri Mar 22 07:25:51 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table                     "TEST_EXP"      74622 rows imported
Import terminated successfully without warnings.