oracle alter session set current_schema

oracle alter session set current_schema

Today I was given a note pad file containing DDL &

DML script and they told me to run as a DEMO user in my Dev Environment.
Here I don’t have Winscp(to copy file from windows to my oracle database in Linux) and I don’t have the password for the DEMO user so in these kind of situations I ran the script as a Demo user by setting the Current_schema=DEMO.

irst I copied the script from note pad file and pasted in Vi editor and named as index.sql.
Because instead of putting the schema name in front of the index name in the entire file it’s better to change the current_schema.Then,

oracle$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 22 16:53:27 2012Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to: 

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning and Automatic Storage Management options
SQL> show user
USER is "SYS"

SQL> alter session set current_schema=DEMO;
Session altered.

SQL>show user
USER is "SYS"
Still it is SYS user…………

SQL> select sys_context('USERENV','SESSION_USER') current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;

CURRENT_USER                      CURRENT_SCHEMA
------------------                            -----------------------------
SYS                                            DEMO



Ran the script which is copied from SQL prompt

SQL> @/opt/oracle/index.sql
You can perform any operation as if you logged in as DEMO After finish doing the job then set back to the sys user again using the same command

SQL> alter session set current_schema=SYS;

SQL> select sys_context('USERENV','SESSION_USER') current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;

CURRENT_USER                     CURRENT_SCHEMA    
-----------------------                    --------------------------
SYS                                           SYS



SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning and Automatic Storage Management options.
oracle$  sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 22 17:56:09 2012Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning and Automatic Storage Management options

SQL> select sys_context('USERENV','SESSION_USER') current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;

CURRENT_USER                     CURRENT_SCHEMA
----------------------                      -------------------------------
SYS                                            SYS



So, if you exit from SQL and reconnect, your default schema name is no more DEMO.

NOTE:  The same work can be done using the TOAD by setting the current Schema to Demo in the Schema Browser page, it is quiet easier if you have Toad