Friday, January 31, 2014

DataPump impdp Raises Error ORA-942 When Creating Index

While importing a database using impdb you can get ORA-00942 error during an index creation. As you know ORA-00942 means table or view does not exist. So when you see this error in an index creation, Oracle actually says that : I cant create the index because the underlying table is not there, even if it is there I can not the see the table because you dont have necessary privileges :)
That is, this error can be generated if the calling schema/user doesn't have the required privileges for the action, as well
Also note that even if we do the import with sys user , we can still get this error in an import process..
The reason behind that is the design of impdb.. So it is expected.. That is ; When impdp creates indexes, it connects to the schema owner before issuing the CREATE INDEX command. So if the schema owner does not have the privilege to create an index on a table, the error ORA-00942 is generated.

For the solution, grant necessary privileges to the user which is the owner of the index..
Note that , you can always create an index later.. So if you have done your import and if you dont want to repeat it, then parse the logfile, find the problematic index creations and create them manually.

No comments :

Post a Comment