ORA-00942 Table or View Does Not Exist Oracle Error

Cause: java.sql.SQLException: ORA-00942: table or view does not exist

Then I try to run the sql statement that the webapp trying to execute before hitting this error and it returns the results without any error. The vendor give me the feedback that the table or the synonyms might be missing but I check and both of them exists. Since this is an UAT Database, so I just create a backup copy of that particular  table and I start to rebuild the table from the grounds up. Then I finally discover the root cause of this incident. It is caused by the webapp user didn’t have access right to this object. We had did some maintenance job of that particular table recent days and it involve cloning and housekeeping and seems like the access privileges of that table get lost in these operation. After I grant back the access right and the dayend job runs without a hitch.

 

Since there are so many possible reason causing this error, below are the steps that may help you identify your root cause:

1. Check existing user tables and views if they exists in Oracle by querying the data dictionary by executing the following SQL statement:select *
   from all_objects
   where object_type in (’TABLE’,’VIEW’)
   and object_name = ‘OBJECT_NAME‘;

    

   Replace OBJECT_NAME with the name of the table or view that you want to verify its existence.

   If this error occurred because the table or view does not exist, take the following actions:

   • Check and ensure that the spelling of the table (does not misspell) or view name is correct.
   • Check and ensure that a view is not specified where a table is required.
   • If no such table or view exists, create the table or view, or use another table or view.
2. If the table or view exists, check and verify if the user has the necessary permissions and rights to read and access (i.e. select) from the table, as certain privileges may be required to access the table. In this case, you will need to have the owner of the table or view, or a Oracle DBA to grant the appropriate privileges, permissions or rights to this object.Note that when selecting from a system view (such as v$session) the privilege or access rights to select from the view must be granted to the user directly and not via a role. This is the case if you can select from the view without problem but then get this error when trying to create a view on it.
3. If the table or view exists but is in a different schema from the current schema where the SQL is executing (in other word, the table doesn’t own by you, but owned by other user), the ORA-00942 error will return too. Resolve this by explicitly reference the table or view by specifying the schema name, i.e. schema_name.table_name.