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 WHP2

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 elvis.sale@new_db
2 /
Synonym created.

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;

COUNT(*)
———-
3906455

SQL>

Advertisements