Accessing a table without specifying the schema name

Question

I have a schema called GBO_ARC_SCHEMA, in which I have one table called TEST_EMP, and I have two users say USER_A and USER_B.

First I connected to USER_A and fired below query

select count(*)from TEST_EMP;

count
-----
20

After that I connected as USER_b and fired below query but it is giving an error, saying that table or view does not exit

select count(*)from TEST_EMP;

But if I use scma.object name it is allowing me to query like below

select count(*)from GBO_ARC_SCHEMA.TEST_EMP;

but as per my requirement I don't want to specify schema name.

can somebody help me out?

Solution

If you want all users to be able to select from the table without qualifying with the schema name, you want to create a public synonym:

create public synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

If you only want user_b to omit the schema name, you want to create a private synonym WITHIN user_b's schema (that is logged on as user_b)

create synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

If you insist on not using synonyms, then, after logging in, do a

alter session set current_schema = GBO_ARC_SCHEMA;

Comments

[René Nyffenegger]

Excuse me, but where do you see sequences?

[Ashok Kumar Dabbadi]

thanq so much friends i have considered alter session set current_schema = GBO_ARC_SCHEMA;

[Ashok Kumar Dabbadi]

alter session set current_schema = GBO_ARC_SCHEMA; is working fine now

[William Robertson]

This is Oracle, not Postgres.