oracle ORA-01775：同义词循环链

ORA-01775: looping chain of synonyms

提问by Adrian

I am creating a table (here below is the code) - this is executed through some script on unix. The script also creates some synonyms (not sure what/how):

我正在创建一个表（下面是代码） - 这是通过 unix 上的一些脚本执行的。该脚本还创建了一些同义词（不确定是什么/如何）：

drop table BNS_SAA_MESSAGES;
CREATE TABLE BNS_SAA_MESSAGES
(
HostNumber varchar(50) NOT NULL,
SAAMessage varchar(2048) NOT NULL,
PRIMARY KEY (HostNumber)
);

I'm getting the following error:

我收到以下错误：

Processing bns_saa_messages
cat: cannot open bns_saa_messages.sql

Commit complete.

GRANT SELECT ON bns_saa_messages TO RL_ORDFX_RPT

GRANT SELECT ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT INSERT ON bns_saa_messages TO RL_ORDFX_RPT

GRANT INSERT ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT UPDATE ON bns_saa_messages TO RL_ORDFX_RPT

GRANT UPDATE ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT DELETE ON bns_saa_messages TO RL_ORDFX_RPT

GRANT DELETE ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


create public synonym bns_saa_messages for ORDMSO.bns_saa_messages

create public synonym bns_saa_messages for ORDMSO.bns_saa_messages
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object



Commit complete.

I googled for ORA-01775: looping chain of synonymsand it seems to mean that a something was removed but there is a pointer to it. I'm guessing it happens during selectfrom 'things' that do not exist anymore. Not sure if these things are tables or something else. This is what I gathered from my research.

我用谷歌搜索ORA-01775: looping chain of synonyms，这似乎意味着某个东西被删除了，但有一个指向它的指针。我猜它发生在select不再存在的“事物”期间。不确定这些东西是桌子还是其他东西。这是我从我的研究中收集到的。

Any idea how to create my tables? I tried to execute multiple times the SQL code, but to no avail - I get the same error every time.

知道如何创建我的表吗？我尝试多次执行 SQL 代码，但无济于事 - 我每次都遇到相同的错误。

Also the table is not created:

也没有创建表：

SQL> select * from bns_saa_messages;
select * from bns_saa_messages
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

I've looked at the following so questions, but it doesn't seem to be helping. Maybe you can see something I don't:
I get an ORA-01775: looping chain of synonyms error when I use sqlldr
How to debug ORA-01775: looping chain of synonyms?
ORA-01775: looping chain of synonyms but there are no synonyms

我查看了以下问题，但似乎没有帮助。也许你可以看到我没有看到的东西：
当我使用 sqlldr 时，我收到一个 ORA-01775：同义词循环链错误
如何调试 ORA-01775：同义词循环链？
ORA-01775: 同义词循环链，但没有同义词

Thanks

谢谢

:UPDATE:

：更新：

After executing: select * from all_synonyms where synonym_name = 'BNS_SAA_MESSAGES';as per Craig's suggestion I get:

执行后：select * from all_synonyms where synonym_name = 'BNS_SAA_MESSAGES';根据克雷格的建议，我得到：

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
PUBLIC                         BNS_SAA_MESSAGES
ORDMSO                         BNS_SAA_MESSAGES

:UPDATE: 2
Running: select * from all_tables where table_name = 'BNS_SAA_MESSAGES';

：更新：2
运行：select * from all_tables where table_name = 'BNS_SAA_MESSAGES';

SQL> select * from all_tables where table_name = 'BNS_SAA_MESSAGES';

no rows selected

回答by Craig

I would run this to see where the synonym is actually pointing:

我会运行它以查看同义词实际指向的位置：

select *
from all_synonyms
where synonym_name = 'BNS_SAA_MESSAGES'

I am guessing the synonym is pointing to the wrong TABLE_OWNER.

我猜同义词指向错误的 TABLE_OWNER。

UPDATE

更新

So where is the table actually at? You can find this using:

那么桌子实际上在哪里？您可以使用以下方法找到它：

select *
from all_tables
where table_name = 'BNS_SAA_MESSAGES'

If table_owner is not 'ORDMSO', then you need to either update the synonym to point to the correct location or run the Create table... script as ORDMSO.

如果 table_owner 不是“ORDMSO”，那么您需要更新同义词以指向正确的位置，或者以 ORDMSO 的身份运行 Create table... 脚本。

UPDATE2

更新2

Can you run the Create table... script as ORDMSO? If not, you are going to need to have someone with higher privileges run:

您可以将 Create table... 脚本作为 ORDMSO 运行吗？如果没有，您将需要让具有更高权限的人运行：

select *
from dba_tables
where table_name = 'BNS_SAA_MESSAGES'

to figure out where the table really is, and then update the synonym accordingly.

找出表格的真正位置，然后相应地更新同义词。

回答by Alex Poole

It looks like the output is from running this a second time, which you hinted at; the ORA-00955 from the create public synonymshows that has been done before somewhere (as does the all_synonymsquery you ran), and it clearly exists from the other errors. You wouldn't have got exactly these errors the first time you ran it, but would on every subsequent run.

看起来输出是第二次运行的，这是你暗示的；ORA-00955 来自create public synonym之前在某处完成的节目（就像all_synonyms您运行的查询一样），并且它显然存在于其他错误中。您在第一次运行时不会遇到这些错误，但在随后的每次运行中都会遇到这些错误。

At a mimimum the first code snippet should drop the public synonym before dropping the table, if you want it to be rerunnable.

最起码第一个代码片段应该在删除表之前删除公共同义词，如果您希望它可以重新运行。

But the first snippet doesn't seem to be run at all. There are no success or failure messages from Oracle. The only real clue to why is this:

但是第一个片段似乎根本没有运行。没有来自 Oracle 的成功或失败消息。原因的唯一真正线索是：

Processing bns_saa_messages
cat: cannot open bns_saa_messages.sql

Which is a shell script problem, not really an Oracle one. Without seeing the shell script it's rather hard to tell quite what's wrong, but I suspect the script is building a temporary .sqlfile from various sources and then running it through SQL*Plus; but the vital bns_saa_messages.sqlfile is missing. Presumably that's where the first snippet is supposed to be; and since that seems to exist, this could beas simple as a name mismatch between the file and what the script is expecting, or the script is doing a cdand the file is in the wrong directory, or something equally trivial. But maybe not... not enough info.

这是一个 shell 脚本问题，而不是真正的 Oracle 问题。如果没有看到 shell 脚本，很难说到底出了什么问题，但我怀疑该脚本正在.sql从各种来源构建一个临时文件，然后通过 SQL*Plus 运行它；但bns_saa_messages.sql缺少重要文件。大概这就是第一个片段应该在的地方；并且由于这似乎存在，这可能很简单，因为文件与脚本期望的名称不匹配，或者脚本正在执行cd并且文件位于错误的目录中，或者同样琐碎的事情。但也许不是……没有足够的信息。