ORA-00942: Can select from "schema.table" but not "table"?

Question

I experienced an ORA-00942 ("table or view does not exist") when executing

select * from brunch

However, no such problem when executing

select * from joe.brunch

May i know what is the issue here?

Answer 1

Unqualified, BRUNCH refers to a different object than JOE.BRUNCH in your current session. You've got a couple of options to fix that.

1. Create a public synonym. This will allow any user that has privileges on the JOE.BRUNCH table to access it by querying BRUNCH

   CREATE PUBLIC SYNONYM brunch FOR joe.brunch

2. Create a private synonym. This will allow just the current user to access the JOE.BRUNCH table by querying BRUNCH

   CREATE SYNONYM brunch FOR joe.brunch

3. Change the current schema for the current session to JOE. This will cause all unqualified references in the current session to resolve to the JOE schema rather than to the current user's schema

   ALTER SESSION SET current_schema = JOE

Answer 2

There are several possible causes

1) there is more than one object (table,view, procedure, etc) called brunch. Oracle does not know which one you are referring to.

2) most likely cause: the table exists in the joe schema but you are connecting as another user who has not been granted select on the joe.brunch object

Try

Grant select on joe.brunch to your_user

and try this and see how many objects match the name brunch

select * from all_objects where object_type in (‘TABLE’,'VIEW’) and object_name = ‘brunch‘;