Switching To A Different Schema

Many times connect with our user database, but we work with objects in other schemas, for which we just prefix the owner of the object that we reference. Oracle gives us the following command to work from our scheme into other schemes;

 

ALTER SESSION SET CURRENT_SCHEMA = <schema name>

 
This is a very old functionality, remember I have used it since version 6 and 7 of Oracle, Here’s an example for a select on a table in another schema (S_EVT_ACT). I’m logged in my session with my user “ANR*BBDDSIE>”  and I can work in the SIEBEL scheme  running;

 

ALTER SESSION SET CURRENT_SCHEMA = SIEBEL

 

And the output from a sql * plus;

 

-- NEXT SELECT FAIL BECAUSE S_EVT_ACT TABLE DONT EXITS IN MY SCHEMA
ANR*BBDDSIE> select * from s_evt_act;
select * from s_evt_cat
              *
ERROR en línea 1:
ORA-00942: table or view does not exist


Transcurrido: 00:00:00.07

-- CHANGE THE DEFAULT SCHEMA ----
ANR*BBDDSIE> alter session set current_schema = SIebEl;

Sesión modificada.

Transcurrido: 00:00:00.04

-- AND RETRY AGAIN ----
ANR*BBDDSIE> select * from s_evt_act;

-- WORKS FINE AND RETURN ROWS FROM SIEBEL SCHEMA ----

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s