Suppose their are two database environment (prod legacy and prd exadata). I am the user and have select privilege on both db having same schema name A.
My current session is "prod legacy"
If I run
alter session set current_schema = A;
alter session set current_schema = B;
A database is composed of multiple schemas (btw, in Oracle, a schema is is pretty much the same as a user), and each schema can contain different objects, such as tables, views, etc.
Which database you connect to is determined by which connection string you used when establishing a database session, and cannot be changed for the lifetime of that session.
Let's say that in your database, you have 2 schemas (users):
B, and they both contain a table called
tbl. When you query:
select * from tbl
...which one does it query? That's where the
current_schema setting comes in. By default, it's the schema of the user you connected with. So if you connected with user
A, then you'll be querying
A.tbl by default.
If you instead want to query
B.tbl while connected as user
A, you have 2 options:
select * from B.tbl
alter session set current_schema = Bcommand to change the default schema to
B, so that when you say
select * from tbl, it will translate it automatically to
select * from B.tbl.
Also note that, just using the
alter session set current_schema command doesn't automatically grant you permissions on the objects of that schema. All it does is interpret your queries differently when you don't explicitly qualify the object names. But you need to have been granted the necessary permissions to query those objects successfully.