Oracle ORA-00942: table or view does not exist

Fix Oracle ORA-00942 when a table exists but is not visible due to schema qualification, synonyms, or missing grants.

intermediatedebuggingoracledatabasepermissions

Symptoms

ORA-00942: table or view does not exist
The table is present in another schema but the query fails
It works as one user and fails as the application user

Possible causes

The table belongs to another schema and is not qualified or synonymed
The application user lacks SELECT/INSERT privileges on the table
The name is case-sensitive and quoted with the wrong case
A synonym points at a table that no longer exists

Diagnosis steps

Check visibility as the connecting user

SELECT table_name FROM all_tables WHERE table_name = 'USERS';

Interpretation

A row means the table exists in a schema you can see; no row means you lack privileges or the name is wrong.

Inspect grants on the object

SELECT * FROM all_tab_privs WHERE table_name = 'USERS';

Interpretation

Absence of the app user in the grant list explains the error despite the table existing.

Look for synonyms in the app schema

Interpretation

Without a synonym or schema prefix, the unqualified name resolves to the wrong schema.

Root cause

ORA-00942 is reported for both a truly missing table and one the current user cannot see. Oracle resolves unqualified names against the connected schema, so a table owned elsewhere is invisible without a synonym or owner prefix, and privileges must be granted explicitly to the app user.

Fix

Qualify the object with its owner or create a synonym in the app schema, and grant the required privileges (SELECT, INSERT, UPDATE, DELETE) to the app user. Match case when quoted identifiers are used. Verify existence via all_tables as the connecting user rather than as a DBA.

Verify the fix

Run the query as the application user; it succeeds, and querying all_tables and all_tab_privs as that user shows the table and the granted privileges.

Symptoms

Queries fail with ORA-00942: table or view does not exist even though DBAs confirm the table exists. It is common when an app connects as a different user than the schema owner.

Diagnosis

Check what the connecting user can actually see:

SELECT owner, table_name FROM all_tables WHERE table_name = 'USERS';
SELECT * FROM all_tab_privs WHERE table_name = 'USERS';

No rows under the app user means visibility, not existence, is the problem.

Fix

Qualify or synonymise, and grant privileges:

CREATE SYNONYM app.users FOR core.users;
GRANT SELECT, INSERT, UPDATE, DELETE ON core.users TO app;

Verification

As the app user, run the original query; it returns rows, and all_tables plus all_tab_privs confirm the object and grants are visible to that user.

Why DBAs and Apps Disagree

The disconnect is one of perspective: a DBA queries dba_tables and sees every object, while the application query is resolved against all_tables, which lists only objects the connecting user can access. That is why a table can be confirmed as existing and still raise ORA-00942. Two habits prevent the confusion — reproduce the error as the connecting user rather than as a privileged one, and grant privileges through roles so all application users inherit the same visibility, instead of granting object-by-object and leaving newly added users unable to see the table.