Oracle db_link和同义词

嗨，汤姆，

我有两个数据库wombat和foo. Womabt has two users craig和denver. I have created a private db_link with the same name in each users of wombat连接到foo如下:

所有者DB_LINK
------ --------------------
克雷格·阿尔布斯·foo.labs.com

丹佛albs.foo.labs.com

我在克雷格为表创建了一个公共同义词fooaudit。但是两个用户都获得了同名表格 “fooaudit”

所有者同义词表名DB_LINK
-
公共abs_synm fooaudit albs.foo.labs.com


Now if I query如下: select * from abs_synm;在任何用户/架构中，db_link同义词将使用craig's or denver's？

公共同义词不必在目标对象中具有所有者资格。例如。

提示: 当我说 “例如” 时，它的意思是 “这是您应该提供的测试用例，无论如何它可能已经回答了您的问题 :-)

--
-- source db
--
SQL> grant create session, create database link, create public synonym to craig identified by craig;

Grant succeeded.

SQL> grant create session, create database link to denver identified by denver;

Grant succeeded.
--
-- target db
--
SQL> grant create session to craig identified by craig;

Grant succeeded.

SQL> grant create session to denver identified by denver;

Grant succeeded.


--
-- source db
--
SQL> conn craig/craig
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from tab@foo;

no rows selected

SQL> create public synonym fooaudit for tab@foo;

Synonym created.

SQL> select * from fooaudit;

no rows selected

SQL> conn denver/denver
Connected.

SQL> create database link foo using 'db122';

Database link created.

SQL> select * from tab@foo;

no rows selected

--
-- Now you might think that this would fail.  
-- After all, DENVER does not have access to CRAIG's objects
--
-- but it works 
--
SQL> select * from fooaudit;

no rows selected
复制

通过查看同义词定义，我们可以看到 * 为什么 *

SQL> select * from dba_synonyms
  2  where synonym_name like 'FOO%'
  3  @pr
==============================
OWNER                         : PUBLIC
SYNONYM_NAME                  : FOOAUDIT
TABLE_OWNER                   :
TABLE_NAME                    : TAB
DB_LINK                       : FOO
ORIGIN_CON_ID                 : 0
复制

TABLE_OWNER为null，我们将在最后一种情况下使用 “DENVER”。

如果你完全限定了同义词，你会得到预期的结果

SQL> drop public synonym fooaudit;

Synonym dropped.

SQL> create public synonym fooaudit for craig.my_table@foo;

Synonym created.

SQL> conn denver/denver
Connected.

SQL> select * from fooaudit;
select * from fooaudit
              *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from FOO


SQL> conn craig/craig
Connected.

SQL> select * from fooaudit;

no rows selected
复制