Re: Help: A difficult requests

Thanks for your reply. If the object in other schema is table/materialized view, it might work, but I just do not know how to control it. Here is the example:
In schema A, there is a table test, which has three rows. And there is a public synonym test, pointing to schema B, which has 0 rows. In schema A:
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 if I rename table test1 back to test, now the schema can see both table test and synonym test, Oracle will still choose synonym, of course that is because of the SQL cache.

My question is, can we somehow control Oracle, through profile or something, let Oracle go directly to synonym, not the table?

Is it possible?

Thanks for your help.

<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.
>
>> Another question, did anybody have any experience with Oracle Transparent
>> Encryption in 10G2? Will that be my saver?
>>
>> Thanks for your help.
>>
>
> David Fitzjarrell
>
Received on Sat Oct 15 2005 - 05:58:47 CDT