Set Default schema for a user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Set Default schema for a user

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    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.
    zm

  2. #2
    Join Date
    Nov 2000
    Posts
    212
    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 .


  3. #3
    Join Date
    Aug 2000
    Posts
    194
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    "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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Posts
    194
    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.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width