oracle-databaseora-00942

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

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?

Solution

  • 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