Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Accessing other schema objects

    Hi....

    Our Oracle DB contains three schemas. S1, S2 and S3. S2 and S3 has its own set of tables and permissions are granted to S1.

    Now my question is, At a given point of time, all the SQLs issued by S1 should point to the tables of S2. Eventhough objects with same name exist in S3 and S1 has appropirate permissions granted. How can I achieve this without prefixing the schema name in the SQL.

    THanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Here 'alnadmin' is your 'S2', i'm logged as 'S1', and i want to access the table (could be a view, package, whatever) alnadmin.text :

    SQL> select count(*) from alnadmin.text;

    COUNT(*)
    ----------
    0

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

    It doesn't work, so :

    SQL> create or replace synonym text for alnadmin.text;

    Synonym created.

    Now it works without the prefix:

    SQL> select count(*) from text;

    COUNT(*)
    ----------
    0

    The 'or replace' clause works in 8i+ (at least). Drop it if in an earlier release.

    HTH
    Al

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Accessing other schema objects

    Easiest way is ALTER SESSION SET CURRENT_SCHEMA=S2;

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    Thanks for your prompt response.

    But the issue is S3 is also have table called text and S1 has access to it.

    Originally posted by alberto.dellera
    Here 'alnadmin' is your 'S2', i'm logged as 'S1', and i want to access the table (could be a view, package, whatever) alnadmin.text :

    SQL> select count(*) from alnadmin.text;

    COUNT(*)
    ----------
    0

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

    It doesn't work, so :

    SQL> create or replace synonym text for alnadmin.text;

    Synonym created.

    Now it works without the prefix:

    SQL> select count(*) from text;

    COUNT(*)
    ----------
    0

    The 'or replace' clause works in 8i+ (at least). Drop it if in an earlier release.

    HTH
    Al

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by gireesh_m
    Thanks for your prompt response.

    But the issue is S3 is also have table called text and S1 has access to it.
    It doesn't matter - think to the synonym as a pointer to the S2 objects:

    If you write

    create or replace synonym text for s2.text;

    select count(*) from text;

    is equivalent to

    select count(*) from s2.text;

    In order to access s3.text you should write explicitly:

    select count(*) from s3.text;

    Al

Posting Permissions

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