Re: Help: A difficult requests

David,

Thanks very much for your reply.

If the object in another schema is not a view, but a table, it might work, but I do not know how to control.

Here is the example:
Schema test has a table test, which has three rows, synonym test, which is pointing to test2.test, which has 0 rows. SQL> select count(*) from test;
 COUNT(*)

         3

SQL> rename test to test1;
Table renamed.

SQL> select count(*) from test;
 COUNT(*)

         0
SQL> rename test1 to test;
Table renamed.

SQL> select count(*) from test;
COUNT(*)

         0

As you can tell, even I rename table test1 back to test, now schema test can see both table test and synonym test, Schema test will choose synonym test. Of course, that is because the SQL cache.

My question is, can we somehow control Oracle, by profile or something, let schema go directly to synonym, not table? Is that possible?

Thanks

<fitzjarrell_at_cox.net> wrote in message
news:1129351264.040744.290930_at_f14g2000cwb.googlegroups.com...
> Comments embedded.
> joe bayer (nospam) wrote:
>> renaming original table is not acceptable, because it will break some
>> other
>> functions.
>> If I do this:
>> In schema A, I have a table A,
>>
>> In schema B, I did
>>
>> Create view A as select * from A.A;
>>
>
> All is well thus far.
>
>> Then I create public synonym A form B.A.
>>
>
> Nothing wrong here.
>
>> Now back to schema A, if I select * from A, will Oracle choose table A,
>> or
>> synonym A? How can we control it?
>>
>
> Oracle will choose the table A owns, not the synonym for the view B
> owns as this view is based upon the table A created. Had Oracle chosen
> to describe the view whilst connected as user A, instead of the table,
> it would create a circular reference which could not be resolved.
> Thus, only the table is considered.
>
>
>
> David Fitzjarrell
>
Received on Sun Nov 13 2005 - 18:59:28 CST