Alydan Consulting - Tip of the Week 74

Tip of the Week (Archived)
Adventures with Export/Import...Oracle 9.2.0.7

Oracle export/import...it just works, right? Simple utility, it's been around forever, everyone uses it, always works even across versions, we don't even think twice about it.

With Oracle 9.2.0.7, we have to think about it. There is a problem, and it looks something like this...

C:\>exp scott/tiger

Export: Release 9.2.0.7.0 - Production on Tue Mar 14 16:18:55 2006

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > emp_table.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > t

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp

. . exporting table EMP 14 rows exported

EXP-00056: ORACLE error 904 encountered

ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.

A similar error occurs if you try import using the file generated above. The explanation is available in the Metalink document 337198.1 Exp 9.2.0.7 Generates Incorrect Syntax When Exporting From Database 10.1.0.2. A change was made in 9.2.0.7 that resulted in that version of export not being compatible with any database version other than 9.2.0.7.

The interesting thing is I received this error while working with a 9.2.0.7 database, as you can see from the export and database version in the above listing. So why would this version compatibility problem show up here?

The answer has to do with how the database was created. It was created using these steps

Install the Oracle software from the CD media, which is version 9.2.0.1.
Apply the 9.2.0.7 patch.
Create the database using the Database Creation Assistant (DBCA)

Even though the 9.2.0.7 patch was applied, the "seed" database files were still from the original 9.2.0.1 installation media. This statement is from the readme for the 9.2.0.7 patch

If you use DBCA to create a new database using the seed database option, some of the components will be from an older version. This is because the seed database was created before the patch release.

To ensure that all database components are at the correct 9.2.0.7 version, the database must be upgraded using the script catpatch.sql. A brief, generic summary of the upgrade steps are (please refer to the Oracle Installation Guide for details and requirements that might be specific to your database):

Navigate to the ORACLE_HOME\rdbms\admin directory
Log into SQL*Plus on the server with sysdba privileges, for example, sqlplus "/ as sysdba"
Shutdown the database
Restart the database using the command startup migrate.
Upgrade the database using the command @catpatch
Recompile any invalid objects with the command @utlrp.
Shutdown and restart the database.

Your 9.2.0.7 export and import should now run properly against your 9.2.0.7 database.

Note: This tip was tested using Oracle9i..

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.

2000 Turnberry Circle, Glenmoore, PA 19343
Voice: (610) 942-1979
Fax: (610) 942-1990
Email

© 2006 Alydan Consulting, Inc.