Synonym resolution oddity

18 Apr, 2005

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>