Search notes:

Oracle: ALTER SESSION SET CURRENT_SCHEMA

user_one creates a table ...

connect user_one/passwordSecretGarden
create table tq84_table_user_one(txt varchar2(20));

... and grants the table to user_two:

grant  all on tq84_table_user_one to user_two;

The user inserts his username into the table:

insert into tq84_table_user_one values(user);
commit;

User two does an analog thing

connect user_two/passwordGardenSecret
create table tq84_table_user_two(txt varchar2(20));
grant  all on tq84_table_user_two to user_one; 
insert into tq84_table_user_two values(user);
commit;

If user two wants to select his own table, he does not need to prefix with his username:

select * from tq84_table_user_two;

However, if he wants to query user one's table, he must prefix it (unless a synonym exists):

select * from user_one.tq84_table_user_one;

If user two finds it tedious to always type user_one's schema, he might choose to set user_one as default schema:

alter session set current_schema = user_one;

The select statement now works perfectly:

select * from tq84_table_user_one;

Note: user_two is still user_one. It's not as though he would magically get some extra privileges. The following statement returns USER_ONE, USER_TWO:

select user, sys_context('userenv','current_schema') from dual;

Also, if user_two queries USER_TABLES, he finds his tables:

select * from user_tables where table_name like 'TQ84_TABLE_USER%';

EXECUTE IMMEDIATE

The execute immediate statement is not influenced by the «current schema», rather, it executes in the context of the connected user.

Determine the name of the current schema

The name of the current schema can be determined with sys_context('userenv','current_schema').

See also identifiying the «own» session.

Transactions are not committed

alter session set current_schema = … does not commit the current transaction.