Re: Changing the default schema for a session

Don=Hall%NA%Contractors_at_netgate.compaq.com wrote:
>
> Somewhere among the many Oracle reference books I ran across an
> undocumented command that allows a user session to change the default
> session schema.

ALTER SESSION SET CURRENT_SCHEMA=new_schema_name;

(sqlplus cmd) SHOW USER returns OLD_SCHEMA (original). SELECT USERNAME from USER_USERS; returns new_schema_name

NOTE: while default schema changes, your priorities, etc do not change.

enjoy
-tm

> Scenario...user1 establishes a connection via SQLPLUS
>
> to select from table1 owned by user1 the command is
> select * from table1 or
> select * from user1.table (not necessary to specify schema is this case)
>
> in order for user1 to select from table2 owned by user2 the command is
> select * from user2.table2 (assuming no synonym and granted privileges)
>
> There is a command that user1 can issue that is something like
> Alter session set schemaname = user2
> Now, even though connected as user1, the session uses user2 as the default
> schema and user1 can now issue the command as
> select * from table2
>
> Is anyone familiar with this particular command, and do you know the
> correct syntax?
Received on Sun Apr 20 1997 - 00:00:00 CDT