Synonym resolution oddity
Well, it seems odd to me anyway….
If you set up a synonym pointing across a database link….
SQL> conn elvis@old_db
SQL> create synonym sale for sale@new_db
2 /
Synonym created.
Other users cannot see it….
SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale
2 /
select count(*) from elvis.sale
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from new_db
Unless you proceed it with the username (in bold below)
SQL> conn elvis/@old_db
Connected.
SQL> drop synonym sale;
Synonym dropped.
SQL> create synonym sale for <strong>elvis</strong>.sale@new_db
2 /
Synonym created.
SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;
COUNT(*)
----------
3906455
SQL>