ORA-00942: table or view does not exist

ORA-00942: table or view "LAB_A"."CUSTOMER" does not exist

Oracle could not find a table or view with that name that you are allowed to see.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-11

ORA-00942 does not always mean the table is missing. Oracle raises it whenever it cannot resolve the name in your current schema, or when the table exists but you have no privilege on it. Oracle deliberately does not tell you which of the two it is.

On recent versions (23ai / 26ai) the message includes the name Oracle actually looked up, such as "LAB_A"."ORDERS". Read that first: the schema part tells you where Oracle searched, and the table part shows how your name was converted to uppercase.

Causes and fixes

  1. The table name is wrong or the table was never created
  2. The table belongs to another schema and you used only its name
  3. The table exists, but you have no privilege on it
  4. The table was created with a quoted, case-sensitive name
  5. In a procedure, privileges granted through a role do not count

1The table name is wrong or the table was never created

The most common case. Here the table is CUSTOMERS, but the query says customer. Before changing anything, search the dictionary with a partial name to see what really exists in your schema.

Setup
Setuprun as lab_a
CREATE TABLE customers (id NUMBER PRIMARY KEY, name VARCHAR2(50));
OUTPUT
Table created.
Reproducerun as lab_a
SELECT COUNT(*) FROM customer;
OUTPUT
SELECT COUNT(*) FROM customer
                     *
ERROR at line 1:
ORA-00942: table or view "LAB_A"."CUSTOMER" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Checkrun as lab_a
SELECT table_name FROM user_tables WHERE table_name LIKE 'CUSTOMER%';
OUTPUT
TABLE_NAME
------------------------------
CUSTOMERS

1 row selected.

If the search returns nothing, the table really does not exist in this schema: create it, or check that you are connected to the right database and user.

After the fixrun as lab_a
SELECT COUNT(*) FROM customers;
OUTPUT
  COUNT(*)
----------
         0

1 row selected.

2The table belongs to another schema and you used only its name

lab_b owns ORDERS and has already granted SELECT to lab_a. But lab_a writes just orders, so Oracle looks for "LAB_A"."ORDERS" — see the schema in the error message — and fails.

ALL_TABLES lists every table you can access, with its owner. Then either qualify the name (lab_b.orders) or create a synonym so the short name keeps working.

Setup
Setuprun as lab_b
CREATE TABLE orders (id NUMBER PRIMARY KEY, amount NUMBER);
GRANT SELECT ON orders TO lab_a;
OUTPUT
Table created.


Grant succeeded.
Reproducerun as lab_a
SELECT COUNT(*) FROM orders;
OUTPUT
SELECT COUNT(*) FROM orders
                     *
ERROR at line 1:
ORA-00942: table or view "LAB_A"."ORDERS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Checkrun as lab_a
SELECT owner, table_name FROM all_tables WHERE table_name = 'ORDERS';
OUTPUT
OWNER                          TABLE_NAME
------------------------------ ------------------------------
LAB_B                          ORDERS

1 row selected.
Fixrun as lab_a
CREATE SYNONYM orders FOR lab_b.orders;
OUTPUT
Synonym created.

Qualifying the name (SELECT COUNT(*) FROM lab_b.orders;) works just as well and needs no extra object. Use a synonym when application code must not change.

After the fixrun as lab_a
SELECT COUNT(*) FROM orders;
OUTPUT
  COUNT(*)
----------
         0

1 row selected.

3The table exists, but you have no privilege on it

The name is fully qualified and correct, yet the result is ORA-00942, not ORA-01031 (insufficient privileges). Oracle hides the existence of objects you are not allowed to see, so a missing grant looks exactly like a missing table.

The owner (or a DBA) has to grant the privilege you need.

Setup
Setuprun as lab_b
CREATE TABLE orders (id NUMBER PRIMARY KEY, amount NUMBER);
OUTPUT
Table created.
Reproducerun as lab_a
SELECT COUNT(*) FROM lab_b.orders;
OUTPUT
SELECT COUNT(*) FROM lab_b.orders
                           *
ERROR at line 1:
ORA-00942: table or view "LAB_B"."ORDERS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Fixrun as lab_b
GRANT SELECT ON orders TO lab_a;
OUTPUT
Grant succeeded.

Grant only what is needed: SELECT for queries, INSERT/UPDATE/DELETE for DML. A DBA can grant on another user's table too, e.g. GRANT SELECT ON lab_b.orders TO lab_a;.

After the fixrun as lab_a
SELECT COUNT(*) FROM lab_b.orders;
OUTPUT
  COUNT(*)
----------
         0

1 row selected.

4The table was created with a quoted, case-sensitive name

CREATE TABLE "orderItems" stores the name exactly as written, in mixed case. Unquoted names are always converted to uppercase, so orderItems becomes ORDERITEMS — which is what the error message shows — and does not match.

This often happens with tables created by ORMs, migration tools or GUI clients. The dictionary shows the real stored name.

Setup
Setuprun as lab_a
CREATE TABLE "orderItems" (id NUMBER PRIMARY KEY);
OUTPUT
Table created.
Reproducerun as lab_a
SELECT COUNT(*) FROM orderItems;
OUTPUT
SELECT COUNT(*) FROM orderItems
                     *
ERROR at line 1:
ORA-00942: table or view "LAB_A"."ORDERITEMS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Checkrun as lab_a
SELECT table_name FROM user_tables;
OUTPUT
TABLE_NAME
------------------------------
orderItems

1 row selected.

Always write the name in double quotes, with exactly the same case: "orderItems". Note that "ORDERITEMS" or "orderitems" would still fail.

After the fixrun as lab_a
SELECT COUNT(*) FROM "orderItems";
OUTPUT
  COUNT(*)
----------
         0

1 row selected.

5In a procedure, privileges granted through a role do not count

lab_a gets SELECT on lab_b.orders through the role lab_reader, and a plain SELECT works (see the setup output). But a stored procedure fails to compile with ORA-00942.

Definer's-rights PL/SQL (the default) ignores privileges received through roles. The owner of the procedure needs a direct grant on the table.

Setup
Setuprun as lab_b
CREATE TABLE orders (id NUMBER PRIMARY KEY, amount NUMBER);
OUTPUT
Table created.
Setuprun as system
DROP ROLE IF EXISTS lab_reader;
CREATE ROLE lab_reader;
GRANT SELECT ON lab_b.orders TO lab_reader;
GRANT lab_reader TO lab_a;
OUTPUT
Role dropped.


Role created.


Grant succeeded.


Grant succeeded.
Setuprun as lab_a
SELECT COUNT(*) FROM lab_b.orders;
OUTPUT
  COUNT(*)
----------
         0

1 row selected.
Reproducerun as lab_a
CREATE OR REPLACE PROCEDURE order_count AS
  n NUMBER;
BEGIN
  SELECT COUNT(*) INTO n FROM lab_b.orders;
  DBMS_OUTPUT.PUT_LINE('orders: ' || n);
END;
/
SHOW ERRORS
OUTPUT
Warning: Procedure created with compilation errors.

Errors for PROCEDURE ORDER_COUNT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
4/37     PL/SQL: ORA-00942: table or view "LAB_B"."ORDERS" does not exist
Fixrun as lab_b
GRANT SELECT ON orders TO lab_a;
OUTPUT
Grant succeeded.

Grant directly to the procedure owner, then recompile.

After the fixrun as lab_a
ALTER PROCEDURE order_count COMPILE;
EXEC order_count
OUTPUT
Procedure altered.

orders: 0

PL/SQL procedure successfully completed.

FAQ

The table exists. Why do I still get ORA-00942?

Usually because it is in another schema and you did not qualify the name, or because you have no privilege on it. Oracle returns the same error in both cases. Check ALL_TABLES (tables you can access) and the schema shown in the error message.

Why ORA-00942 and not ORA-01031 when I lack privileges?

Oracle does not reveal objects you cannot access. Without any privilege on the table, you get ORA-00942. ORA-01031 appears when you can see the object but try an operation you are not allowed to do.

The query works in SQL Developer but fails inside my procedure. Why?

Your privilege probably comes from a role. Roles are active in your session but ignored when compiling definer's-rights PL/SQL. Ask for a direct grant to the procedure owner.

How do I find which schema owns a table?

Run SELECT owner, table_name FROM all_tables WHERE table_name = 'ORDERS'; (use the uppercase name). For views and synonyms use ALL_OBJECTS instead. A DBA can search DBA_OBJECTS, which also shows objects you have no privilege on.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors