From 11g export to 9i import error

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.8   [Release: 9.2 to 9.2]
Information in this document applies to any platform.

Symptoms

You need to export data from 11g and import into 9iR2. For this reason, the exp utility version 9iR2 is used to extract data from 11g. There are two aspects of this problem:

1. On some Unix platforms, the exp utility either returns no errors or crashes with core dumps.

2. On Windows platforms, exp crashes with core dumps (Dr.Watson)

Both created dumps are corrupt, the imp utility crashes immediately after the export dump header is read.

Cause

Exp utility version 9iR2 internally uses the dictionary view EXU9DEFPSWITCHES to get information about some parameters like PLSQL_COMPILER_FLAGS and NLS_LENGTH_SEMANTICS. The parameter PLSQL_COMPILER_FLAGS doesn't exist in 11g anylonger and so the view EXU9DEFPSWITCHES returns unhandled 0 rows.

Solution

1. Connect to database 11g as SYSDBA with SQL*Plus

2. Change the definition of view EXU9DEFPSWITCHES from:

CREATE OR REPLACE VIEW exu9defpswitches (
                compflgs, nlslensem ) AS
        SELECT  a.value, b.value
        FROM    sys.v$parameter a, sys.v$parameter b
        WHERE   a.name = 'plsql_compiler_flags' AND
                b.name = 'nls_length_semantics'


to:

CREATE OR REPLACE VIEW exu9defpswitches (
                compflgs, nlslensem ) AS
        SELECT  a.value, b.value
        FROM    sys.v$parameter a, sys.v$parameter b
        WHERE   a.name = 'plsql_code_type' AND
                b.name = 'nls_length_semantics'


(see new parameter PLSQL_CODE_TYPE)

3. Re-start exp version 9iR2 to extract from 11g

4. Import the new dump using imp version 9iR2.