RE: change users' default schema

First of all, I want to say thank you to everyone replied or replying.
The user is connecting via ODBC and I hope they can easily do "alter session 
set current_schema=?" in ODBC.

Thanks a lot!

Roger

-----Original Message-----
From: Mladen Gogala [mailto:mgogala@xxxxxxxxxxx]
Sent: Thursday, November 02, 2006 3:34 PM
To: bdbafh@xxxxxxxxx
Cc: Roger Xu; Oracle-L@Freelists. Org (E-mail)
Subject: Re: change users' default schema



On 11/02/2006 04:15:42 PM, Paul Drake wrote:
> On 11/2/06, Roger Xu <Roger.Xu@xxxxxxxxxxx> wrote:
> >
> >  Hi List,
> >
> > Can I change a user's default schema, so he dose not need to type
> > SCOTT.EMP just EMP?
> > (This user does nothing but queries SCOTT's tables.)
> > I know "Synonyms" can be created to bypass the prefix requirement.
> >
> > Thanks,
> >
> > Roger Xu
> >
> 
> http://www.oracleadvice.com/Tips/logontrigger.htm
> 
> Or one could use "execute immediate" instead.
> 

It's by far too complicated. Alter session set current_schema will change name 
resolution (not the
privileges!!!!) to the requested schema:

SQL> connect system
Enter password:
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter session set current_schema=scott;

Session altered.

SQL> select * from emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

SQL>


Of course, in order to do that, you must have "BECOME LUSER" privilege.



-- 
Mladen Gogala
http://www.mladen-gogala.com


This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential and/or privileged information. Any 
review, dissemination, copying, printing or other use o
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security 
System.
--
http://www.freelists.org/webpage/oracle-l


Other related posts: