Oracle "ORA-00942: table or view does not exist": when the table is right there

1. Which schema are you in?

In Oracle, a table belongs to a schema, and each user has their own. SELECT * FROM orders looks for your current schema’s table, a public synonym, or nothing.

SELECT USER, SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;

Find where the table is:

SELECT owner, table_name FROM all_tables WHERE UPPER(table_name) = 'ORDERS';

all_tables shows what you can access, and dba_tables shows everything, if you’re allowed to read it. Check all_views too, since it might be a view.

Then name the owner:

SELECT * FROM hr.orders;

Or change the default for your session:

ALTER SESSION SET CURRENT_SCHEMA = hr;

The schema is a usual suspect when an application moves between environments and connects as another user. Coming from another engine, note that a schema here is a user, not a folder in a database. The SQL Dialect Reference compares these differences.

2. No privileges

If the table isn’t in all_tables for you, but an administrator can see it in dba_tables, you lack rights. The owner or an administrator grants them:

GRANT SELECT ON hr.orders TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.orders TO app;

Check what you have:

SELECT owner, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'ORDERS';

3. Names are stored in uppercase

Unquoted names become uppercase. Quoted names stay exactly as written, and that’s where this catches people, often through a tool or an ORM that adds quotes:

CREATE TABLE "orders" (id NUMBER);    -- stored as orders

SELECT * FROM orders;                 -- looks for ORDERS: ORA-00942
SELECT * FROM "orders";               -- works

Check the real spelling:

SELECT table_name FROM user_tables WHERE UPPER(table_name) = 'ORDERS';

Tools show it as stored. If it’s lowercase, either quote it everywhere or rename it, which is better in the long run:

ALTER TABLE "orders" RENAME TO orders;

4. A synonym, so nobody has to type the schema

If applications shouldn’t have to know the owner, use a synonym:

CREATE SYNONYM app.orders FOR hr.orders;

A synonym can point at something that’s gone. Then it fails with the same error, or with ORA-00980: synonym translation is no longer valid. Check it:

SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'ORDERS';

A synonym doesn’t give access, so the GRANT is still needed.

5. It works in SQL, but not in my procedure

The classic one. Your query works in SQL*Plus, and the same query in a stored procedure fails at compile time:

PL/SQL: ORA-00942: table or view "HR"."ORDERS" does not exist

Privileges through a role don’t count inside a stored procedure with the default definer’s rights. Your session has them, and the procedure doesn’t. Grant directly to the user:

GRANT SELECT ON hr.orders TO app;

The same reasoning applies to CREATE VIEW, which also needs a direct grant.

6. It really isn’t there

It could be a missing migration, or the wrong database. Check where you are:

SELECT name, open_mode FROM v$database;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM dual;

On Oracle 12c and later, connecting to the root container (CDB$ROOT) instead of your pluggable database is a common reason why everything seems to have disappeared. Connect with the PDB’s service name.

The table might have been dropped too. If the recycle bin is on, you can get it back:

SELECT original_name, droptime FROM user_recyclebin;
FLASHBACK TABLE orders TO BEFORE DROP;

And in a long script, the error’s line number shows which of the tables it’s about, which you can’t always see in the message.