oracle - 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