ORA-00942 means Oracle could not find the table or view named in a statement, as seen by the current user. The object may really be missing, but just as often it exists in another schema, its name is spelled or quoted differently, or the user has no privilege on it, which Oracle reports the same way so as not to reveal what exists. In Oracle AI Database 26ai the message shows the schema and name Oracle looked for.
Reproduce the Error
Example:
select * from demo_missing; select * from sys.user$;
Output:
Error starting at line : 1 In command - select * from demo_missing Error at Command Line : 1 Column : 15 Error report - SQL Error: ORA-00942: table or view "NIMBUS"."DEMO_MISSING" does not exist Error starting at line : 2 In command - select * from sys.user$ Error at Command Line : 2 Column : 19 Error report - SQL Error: ORA-00942: table or view "SYS"."USER$" does not exist
- DEMO_MISSING does not exist: Oracle looked for it in the current schema, NIMBUS.
- SYS.USER$ exists, but this user has no privilege on it, and Oracle still says it does not exist.
Find Out What Exists
ALL_OBJECTS lists the objects the user can see, in every schema:
Example:
-- does it exist, under which owner, and as what?
select owner, object_name, object_type
from all_objects
where object_name in ('DEMO_MISSING', 'EMPLOYEES')
order by owner;Output:
OWNER OBJECT_NAME OBJECT_TYPE _________ ______________ ______________ NIMBUS EMPLOYEES TABLE
EMPLOYEES exists in NIMBUS, while DEMO_MISSING is not visible anywhere. An object that is missing here either does not exist or needs a grant.
How to Fix It
- Another schema: prefix the owner, as in hr.employees, or create a synonym.
- No privilege: the owner grants it, for example GRANT SELECT ON hr.employees TO app_user.
- Quoted names: a table created as "Orders" must be queried as "Orders", with the quotes and the same case.
- Typos and dropped objects: check the name in ALL_OBJECTS, and the recycle bin for a recently dropped table.
Things to Know
- Privileges granted through a role do not apply inside definer's-rights PL/SQL; the procedure then raises ORA-00942 at compile time. Grant the privilege directly to the owner.
- An application that connects as a different user than the schema owner needs both the grants and the schema prefix or synonyms.
Related Guides
- How to Grant Object Privileges in Oracle
- How to Restore Dropped Tables from the Recycle Bin in Oracle
Conclusion
ORA-00942 means the object is not visible to the current user: missing, in another schema, quoted differently, or not granted. Look it up in ALL_OBJECTS, then prefix the owner, request a grant, or fix the name.
