Wednesday, February 22, 2012

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.

First 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,

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 22 16:53:27 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With 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 Production
With the Partitioning and Automatic Storage Management options
opt/oracle >  sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 22 17:56:09 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With 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




2 comments:

Prashant said...

I have tried its work when I have to do in SQL but in pl/SQL block it doesn't work it only run a command in last user.
CREATE OR REPLACE PROCEDURE conn
AS
USER_REC VARCHAR2(10);
V_USER VARCHAR2(30);
COUN INTEGER;
str varchar2(100);
str1 varchar2(30);
str2 varchar2(30);
str3 varchar2(20);

CURSOR USER_NAME IS SELECT USERNAME FROM ALL_USERS where username = 'TEST';

BEGIN

select count(*) into COUN FROM ALL_USERS where username like 'TEST%';

FOR USER_REC IN USER_NAME
LOOP

IF COUN > 0 then
--str='ALTER SESSION SET CURRENT_SCHEMA='||USER_REC.USERNAME;
V_USER:=USER_REC.USERNAME;
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA='||USER_REC.USERNAME;
select sys_context('USERENV','SESSION_USER') current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema into str1,str2 from dual;
dbms_output.put_line('users : ' || str1);
dbms_output.put_line('schema : ' || str2);
end if;

END LOOP;
END;
/

Anonymous said...

Hi Good information.

Oracle training

MS Dynamics training

Post a Comment

Auto Scroll Stop Scroll