ALTER SESSION SET CURRENT_SCHEMA

Recently I have learned how to use ALTER SESSION SET CURRENT_SCHEMA=schmea. This setting makes subsequent unqualified references to schema objects during the session resolve to objects in the specified schema.

Sometimes, developers give us scripts without qualifying the objects with the schema name, and We DBA usually execute the scripts in our own database user account. So this setting provides a convenient way to resolve this issue, otherwise we may have to modify the script to add schema names. Before I tend to complain about it and emphasize developers should have used qualified name. Actaully, I learned this trick from a developer.

A concern is thus how can I know what is my CURRENT_SCHMEA setting? If I am not careful, I may end up drop a table t that belongs to another schema. It is not difficult to find the answer by google.

The following is the query:

z98xxx4@DBT920> ho cat sess_user_sch.sql
col current_user format a15
col current_schema format a15

select sys_context('USERENV','SESSION_USER') current_user,
sys_context('USERENV','SESSION_SCHEMA') current_schema
from dual
/


z98xxx4@DBT920> create table denis.t as select 'denis' name from dual;

Table created.

z98xxx4@DBT920> create table z98xxx4.t as select 'z98xxx4' name from dual;

Table created.
z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 Z98xxx4

z98xxx4@DBT920> select * from t;

NAME
------------------------------
z98xxx4

z98xxx4@DBT920> alter session set current_schema=denis;

Session altered.

z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 DENIS

z98xxx4@DBT920> select * from t;

NAME
------------------------------
denis