sms sam sms sam - 4 months ago 17
SQL Question

alter session for current schema

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;


Which schema I am accessing: legacy or exadata? Why?

Also, I have access to schema B.

If i run
alter session set current_schema = B;


Why am I able to access schema B based on A's schema configuration.

Note: I am using SQL Developer

Answer

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): A and 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:

  1. You can always explicitly qualify the table name with the desired schema when writing your query. e.g.: select * from B.tbl
  2. You can use the alter session set current_schema = B command 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.