Saturday, January 24, 2015

ORA-00942 table or view does not exist

ORA-00942:

table or view does not exist

This is one of the most common errors which end users faces, returned by the Oracle RDBMS, and the meanings of this error message is that table, view, materialized view or cluster you are trying to access (SELECT, INSERT. UPDATE or DELETE).
There are several different reasons for this error being returned, I will be discussing a few of them in the following and would try to update this document every time I find a new reason for this error message. When I would refer here “Table”, it would mean table, view, materialized view or cluster, unless explicitly either of these objects type is mentioned.

Reason: You have typed the table name wrongly while accessing it.
Solution: Check the name of the table you are trying to access and correct it.

Reason: Table is in another schema and you have not prefixed the table name with the schema name.
Solution: Either create a synonym in your schema for this table which is in a different schema which you are trying to access, or prefix the schema name with the table name while accessing it.

Reason: Table is in another schema and you have typed schema name wrongly while prefixing schema name with the table name.
Solution: Check the schema name and correct it if you have typed schema name wrongly while prefixing it with the table name.

Reason: Table is in another schema and you don’t have any right/privilege on this table to access it.
Solution: Grant appropriate privileges on the table in another schema to the schema which is trying to access this table

Reason: This error can be seen during import process. The reason is that you are importing a table which has a foreign key constraint on it and its parent table was not imported along with this child table. During last step of import when constraints are enabled, constraint validation will fail with this error because parent table was not found during the validation phase of this constraint.


Solution: Export parent and child tables together and then also import them together. If you are looking to import only child table, export the table with option CONSTRAINTS=N so that foreign key constraint is not exported. Lastly, you can simply ignore this error if it does not hurt you because import of data has already been completed successfully.  

No comments:

Post a Comment

Popular Posts - All Times