errorOracleError 00942

Oracle Error 00942: Table or View Does Not Exist

Error Message
ORA-00942: table or view does not exist

What is Oracle Error 00942?

Oracle ORA-00942 occurs when a SQL statement references a table or view that does not exist in the current schema, or the current user lacks SELECT privilege on an object in another schema.

Common Causes

  • 1

    Table does not exist in any accessible schema

  • 2

    Table exists in another user's schema without a synonym or SELECT grant

  • 3

    Querying with wrong schema prefix: user.table_name

  • 4

    Typo in the table name (Oracle table names are uppercase by default)

  • 5

    Missing privilege: SELECT not granted to current user

  • 6

    View references a table that was dropped

Step-by-Step Solutions

1

Check if table exists: SELECT * FROM all_tables WHERE table_name = UPPER('tablename');

2

Find which schema owns it: SELECT owner, table_name FROM all_tables WHERE table_name = UPPER('tablename');

3

Use fully qualified name: SELECT * FROM schema_owner.table_name;

4

Grant access: GRANT SELECT ON schema_owner.table_name TO current_user;

5

Create synonym: CREATE SYNONYM table_name FOR schema_owner.table_name;

Prevention Tips

  • Create PUBLIC or private synonyms for frequently accessed cross-schema objects

  • Grant appropriate SELECT privileges with GRANT...TO

  • Standardize schema naming conventions across the team

  • Use ALL_TABLES, ALL_VIEWS, ALL_SYNONYMS data dictionary views to navigate object ownership

Frequently Asked Questions

How do I find which schema owns a table in Oracle?
SELECT owner, object_name, object_type FROM all_objects WHERE object_name = UPPER('your_table_name'); Or: SELECT owner, table_name FROM all_tables WHERE table_name = UPPER('your_table_name'); If nothing appears, the table doesn't exist or your user has no access to see it even in all_tables.
What is a synonym in Oracle and when do you use it?
A synonym is an alias for a database object (table, view, sequence, procedure). It allows users to reference objects in other schemas without the schema prefix: CREATE SYNONYM employees FOR hr.employees; Then: SELECT * FROM employees; (works without schema prefix). PUBLIC synonyms are accessible to all users: CREATE PUBLIC SYNONYM employees FOR hr.employees;

Related Errors

ORACLE 01017ORACLE 01031ORACLE 00904

Still Stuck?

Ask our AI SQL Assistant or the community — get answers in seconds.

Ask AI Assistant Test in Playground