Oracle 10g import function-based indexes error

Symptom Brief Description: Index creation on import fails with error 942, error message states that tables do not exist which do, in fact, exist.

I ran into this issue over the weekend...

When running an import, I found a number of logged errors indicating that the import could not create an index on a table because the table did not exist.

Fair enough -- it's perfectly reasonable that an index can't be created on a table that doesn't exist. The problem, of course, is that all of the tables referenced did, in fact, exist. This seemed a little 'un-sporting' to me, given the fact that I was looking right at the table(s) . After checking the obvious possible causes (listed below), I noticed that all of the indexes in question were function based which led to finding the answer on metalink. This is a somewhat confusing error message related to a 10.2.0.3 bug which has been fixed in 10.2.0.4. Details are below.


Symptom:

• On import, some indexes fail on creation with the error below (ORA-00942).
• On investigation, the table does, in fact, exist.
• The indexes in question are function-based

Double quotes are produced by logging function and do not indicate a syntax problem in the original statement.

IMP-00017: following statement failed with ORACLE error 942:
"CREATE UNIQUE INDEX "indexname" ON "tablename" (UPPER("column1"
"ID") , "column2" ) TABLESPACE "tablespace" NOLOGGING"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist

Things that should be ruled out:

• Verify that the table exists (if it doesn't the error is telling you the truth)
• Verify that the user running the import has permissions to the table.
• Verify that the index is not referencing a foreign key which does not yet exist.

The actual problem:

The problem is addressed in Oracle Metalink article/bug 5391326.
The problem is specific to the import of function-based indexes, run as SYS, on versions of 10gR2 less than 10.2.0.4.
If you're already on 10.2.0.4 this isn't your problem.

Workaround:

Run the import as a user other than SYS (with the required permissions).

Fix:

This is fixed in 10.2.0.4