ORA-00942: Table or View Does Not Exist — Causes, Fixes & Prevention
ORA-00942 is one of the most frequently encountered errors in Oracle Database, triggered when a SQL statement references a table or view that either does not exist or is not accessible to the current user. Importantly, Oracle intentionally returns this same error whether the object truly doesn't exist or the user simply lacks privileges — this is a deliberate security design to prevent unauthorized users from discovering schema information.
Top 3 Causes and Fixes
Cause 1: The Object Simply Does Not Exist (Typo or Missing Deployment)
The object name may be misspelled, or the table exists in the development environment but has not yet been deployed to production.
-- Check if the table exists in the current schema
SELECT table_name
FROM user_tables
WHERE table_name = 'EMPLOYEES'; -- Always use UPPERCASE
-- Search across all accessible schemas
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name LIKE '%EMPLO%'
AND object_type IN ('TABLE', 'VIEW');
-- Check with DBA privileges (full database search)
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name = 'EMPLOYEES';
Fix: Verify the exact object name and ensure it has been created in the target environment. Remember that Oracle stores object names in UPPERCASE by default unless created with double quotes.
Cause 2: Insufficient Privileges on the Object
The table exists, but the current user has not been granted the necessary privileges to access it. This is extremely common in environments where the application schema is separate from the object owner schema.
-- Grant SELECT privilege to a specific user
GRANT SELECT ON hr.employees TO app_user;
-- Grant multiple DML privileges at once
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;
-- Verify current privileges on a table
SELECT grantee, owner, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMPLOYEES'
AND grantee = 'APP_USER';
-- Create a synonym so the user can access without schema prefix
CREATE SYNONYM employees FOR hr.employees;
-- Create a public synonym (requires DBA privilege)
CREATE PUBLIC SYNONYM employees FOR hr.employees;
Fix: Have the object owner or a DBA grant the required privilege. Create synonyms to simplify access without requiring schema-qualified names.
Cause 3: Missing or Incorrect Schema Prefix
When a user accesses a table owned by a different schema without specifying the schema name, Oracle looks only in the current user's schema and fails to find the object.
-- WRONG: Fails if EMP is owned by SCOTT, not the current user
SELECT * FROM emp;
-- CORRECT: Qualify with schema name
SELECT * FROM scott.emp;
-- Alternatively, change the current schema for the session
ALTER SESSION SET CURRENT_SCHEMA = scott;
-- Now you can query without the schema prefix
SELECT * FROM emp;
-- Confirm the active schema
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema
FROM dual;
Fix: Always qualify table references with the schema name when accessing objects across schemas, or use synonyms to abstract the schema dependency.
Quick Diagnosis Checklist
-- 1. Does the object exist anywhere in the database?
SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = 'EMPLOYEES';
-- 2. Does a synonym exist pointing to it?
SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'EMPLOYEES';
-- 3. Does the current user have ANY TABLE privileges?
SELECT privilege
FROM user_sys_privs
WHERE privilege LIKE '%ANY TABLE%';
Prevention Tips
Standardize privilege grants in deployment scripts.
Always bundle GRANT statements alongside your CREATE TABLE DDL scripts. Treat privilege grants and synonym creation as mandatory steps in your deployment checklist — not optional afterthoughts. Regularly audit privileges using all_tab_privs to catch missing grants before they cause production issues.
Enforce naming conventions and sync schemas across environments.
Use version control (e.g., Git) for all DDL changes and regularly compare schema states between development, staging, and production using DBMS_METADATA or a schema comparison tool. Establish a team-wide rule to always create objects with UPPERCASE names and avoid double-quoted identifiers to prevent case-sensitivity surprises.
Related Oracle Errors
| Error Code | Description |
|---|---|
| ORA-00904 | Invalid column identifier — column name doesn't exist or is misspelled |
| ORA-01031 | Insufficient privileges — higher-level system privilege is missing |
| ORA-00980 | Synonym translation no longer valid — the target object was dropped |
| ORA-06550 | PL/SQL compilation error — often wraps ORA-00942 in stored procedures |
📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.
Top comments (0)