Error Ora-00942 means you are attempting to execute an SQL statement that references a table or view which does not exist. There are several possible causes for the “table or view does not exist” error, including:
- Referencing a table or view that does not exist
- Using an unauthorized synonym
- Using an expression of view where a table is required
- Attempting to use a table without proper permission or privilege
With any error, always view the oerr command first to see details of your error. View the oerr command and follow the following solution tips on how to resolve the error.
View the Data Dictionary
To check that the table or view exists, query the data dictionary to view a list of all existing tables and views (shown below). Make sure the table and view names have been spelled correctly, and the view is not referenced where a table is needed.
Execute the following query to view the data dictionary:
SELECT *
FROM all_objects
WHERE object_type IN (‘TABLE’,’VIEW’)
AND object_name = ‘OBJECT_NAME’;
Check that the View, Table or Synonym Exists
A common cause of the Ora-00942 error is that the table or view has not been created. If you are unsure if the view, table or synonym exists, execute the following query:
SELECT *
FROM DBA_TABLES
WHERE TABLE_NAME = ‘table_name’;
SELECT *
FROM DBA_SYNONYM
WHERE SYNONYM_NAME = ‘synonym_name’;
SELECT *
FROM DBA_VIEWS
WHERE VIEW_NAME = ‘view_name’;
Reference the Correct Schema
You may be seeing the Ora-00942 error because you are referencing a table or view in a schema which you did not create but one that is in another schema. To correctly execute the query from another schema, you must reference the table by the schema name.
SELECT *
FROM schema_name.table_name;
If you are unsure which schema the table or view belongs to, execute the following query:
SELECT owner
FROM all_objects
WHERE object_type IN (‘TABLE’,’VIEW’)
AND object_name = ‘table_name’;
If the table has not been created and you do not have the proper privileges, you will need to contact the database administrator. If none of the above causes seem to be the issue, check that the database connection has been configured correctly. While this cause is not as common as the above listed, your server may not have successfully established connection to the database. To avoid seeing error Ora-00942 in the future, review the database and know which tables and views are in which schema prior to executing queries. Double check spelling and be aware of your user privileges. Consulting an Oracle partner firm is a beneficial route to implementing Oracle software across your business.