Reference a table in other schema omiting schema name

Question

If I have a table sch1.tab1 is it possible to call it from schema/user sch2 just with select * from tab1 (assume that we have all the privilegies)?

I am aware that in postgresql you can set the search path where db would look for tables which enables you to omit the schema when you are referencing a table but I do not know if this exists in oracle.

Thank you.

Thoughtout

Answer 1

You can create a synonym, but you'd have to make one for each table you wanted to access; from sch2:

create synonym tab1 for sch1.tab1;

A more general method is to switch your current schema:

alter session set current_schema = 'SCH1';

You're still connected with your original user account and only have those privileges still, but you don't have to qualify objects in that schema any more. But now you would have to qualify any of your own tables (back in sch2), if you have objects in both schemas.

Linage