Ashok Kumar Dabbadi Ashok Kumar Dabbadi - 4 months ago 14
SQL Question

Accessing a table without specifying the schema name

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?

Answer

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