| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Changing the default schema for a session
It is alter session set current_schema=USERNAME;
it does not affect access priveleges, nor does it affect the value of select user from dual;
It simply changes the default USER name that will be glued on front of referenced objects in queries and such.
It is not documented but exp (the export utility) uses it a lot....
SQL> desc emp
Object does not exist.
SQL> alter session set current_schema=scott;
Session altered.
SQL> desc emp
Name Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
SQL>
On Thu, 17 Apr 1997 15:35:40 GMT,
<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.
>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?
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |