Set Default schema for a user

[mzhu]

The USER2 has no his own schema objects and he just want to access the USER1's schema. When USER2 accesses USER1'object, he always needs to prefix USER1 to the object name (For Example, select * from USER1.table1).

My question is: is there any way to set USER1's schema as the default schema for USER2, so USER2 doesn't need to use the prefix any more?

I don't want to use synonym as USER1's schema could be dynamic.

[LND]

in old days we used private synonyms for every user. If have see case designer for example of this.
now alter session set current_schema is officialy available.
At least I am considering to use this approach inb future applications .

[gsprince]

Hi LND:

Can you point me to the document which says its officially supported by Oracle?

As for as I know, this is an un-documented command and has some bugs. (ex. Alter table myTab add column myCol datatype DEFAULT def, create view ..)

Thanks.

[jmodic]

"ATER SESSION SET CURRENT SCHEMA = schema;" is perfectly legal SQL command, documented in Oracle8i SQL manual. SO it is not unsupported any more, as it trully was up until 8i.

[gsprince]

Thanks Jmodic,

But I still have the problems with the mentioned SQLs. Any workarounds. (In these spl cases, I prefix the object name with the owner_name),

I have oracle 8.1.6.

[jmodic]

SQL> connect system/manager@o815
Connected.
SQL> select * from scott.dept;

DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter session set current_schema=scott;

Session altered.

SQL> select * from dept;

DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON