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.
- 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
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 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.
Run the import as a user other than SYS (with the required permissions).
This is fixed in 10.2.0.4