我使用sqlldr时收到ORA-01775:循环的同义词链错误 [英] I get an ORA-01775: looping chain of synonyms error when I use sqlldr

问题描述

我很抱歉在互联网上发布了一个似乎已经被问过很多次的问题，但是由于某种原因我不能完全解决它.

I apologize for posting a question that seems to have been asked numerous times on the internet, but I can't quite fix it for some reason.

我试图使用Oracle神奇的sqldr实用程序填充某些表，但是由于某种原因，它引发了ORA-01775错误.

I was trying to populate some tables using Oracle's magical sqldr utility, but it throws an ORA-01775 error for some reason.

在Google上访问的每个地方，人们都会说出类似的话:业余，把你的同义词整理出来"(这是释义的)，这很不错，但是我没有做任何同义词.

Everywhere I go on Google, people say something along the lines of: "Amateur, get your synonyms sorted out" (that was paraphrased) and that's nice and all, but I did not make any synonyms.

在这里，以下内容在我的系统上不起作用:

Here, the following does not work on my system:

SQLPLUS user/password
SQL>CREATE TABLE test (name varchar(10), id number);
SQL>exit

然后，我有一个.ctl文件，内容如下:

Then, I have a .ctl file with the following contents:

load data
    characterset utf16
    infile *
    append
    into table test
    (name,
     id
    )
    begindata
    "GURRR"  4567

然后我运行以下命令:

sqlldr user@localhost/password control=/tmp/controlfiles/test.ctl

结果:

SQL*Loader-702: Internal error - ulndotvcol: OCIStmtExecute()
ORA-01775: looping chain of synonyms

test.log的一部分:

Part of test.log:

Table TEST, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NAME                                FIRST     2           CHARACTER            
ID                                   NEXT     2           CHARACTER            

SQL*Loader-702: Internal error - ulndotvcol: OCIStmtExecute()
ORA-01775: looping chain of synonyms     

而且，如果我尝试手动插入:

And, if I try to do a manual insert:

SQL> insert into test values ('aa', 56);
1 row created.

没问题.

所以，是的，我被卡住了！

So, yeah, I am stuck!

如果有帮助，我在CentOS上使用Oracle 11g XE.

If it helps, I am using Oracle 11g XE on CentOS.

感谢帮助人员，我非常感谢.

Thanks for the help guys, I appreciate it.

某种程度上，我想出了问题的一部分.问题在于，在某个地方，可能是在失败的加载期间或某些时候，Oracle给了自己损坏的视图和同义词.

I kind of, sort of figured out part of the problem. The problem was that somewhere along the line, maybe during a failed load or something, Oracle had given itself corrupt views and synonyms.

受影响的视图为:GV_$LOADISTAT，GV_$LOADPSTAT，V_$LOADISTAT和V_$LOADPSTAT.我不太确定为什么视图损坏了，但是重新编译它们会导致compiled with errors错误.查询本身中使用的同义词已损坏，即gv$loadistat，gv$loadpstat，v$loadistat和v$loadpstat同义词.

The affected views were: GV_$LOADISTAT, GV_$LOADPSTAT, V_$LOADISTAT and V_$LOADPSTAT. I am not quite sure why the views got corrupt, but recompiling them resulted in compiled with errorserrors. The synonyms used in the queries themselves were corrupt, namely the gv$loadistat, gv$loadpstat, v$loadistat and v$loadpstat synonyms.

我不确定为什么会这样，而且我不太了解.因此，我决定删除同义词并重新创建它们.不幸的是，我无法重新创建它们，因为它们所指向的视图(这里有一些奇怪的递归……)已损坏.这些视图是前面提到的GV_$LOADISTAT和其他视图.换句话说，同义词指向使用这些同义词的视图.谈论循环链.

I wasn't sure about why this was happening and I didn't quite understand anything. So, I decided to drop the synonyms and recreate them. Unfortunately, I couldn't recreate them, as the view they pointed to (there is a bit of weird recursion going on here...) was corrupt. These views were the aforementioned GV_$LOADISTAT and other views. In other words, the synonyms pointed to the views that used those synonyms. Talk about a looping chain.

所以...我重新创建了公共同义词，但没有将视图指定为GV_$LOADISTAT，而是将它们指定为sys.GV_$LOADISTAT.例如

So...I recreated the public synonyms but instead of specifying the view as GV_$LOADISTAT, I specified them as sys.GV_$LOADISTAT. e.g.

DROP PUBLIC synonym GV$LOADISTAT;
CREATE PUBLIC synonym GV$LOADISTAT for sys.GV_$LOADISTAT;

然后，我重新创建了用户视图以指向那些公共同义词.

Then, I recreated the user views to point to those public synonyms.

CREATE OR REPLACE FORCE VIEW "USER"."GV_$LOADISTAT" ("INST_ID", "OWNER", "TABNAME", "INDEXNAME", "SUBNAME", "MESSAGE_NUM", "MESSAGE")
 AS
 SELECT "INST_ID",
   "OWNER",
   "TABNAME",
   "INDEXNAME",
   "SUBNAME",
   "MESSAGE_NUM",
   "MESSAGE"
 FROM gv$loadistat;

这似乎可以解决视图/同义词.是的，虽然有点破烂，但它以某种方式起作用了.不幸的是，这还不足以运行SQL Loader.我出现了table or view does not exist错误.

That seemed to fix the views/synonyms. Yeah, it is a bit of a hack, but it somehow worked. Unfortunately, this was not enough to run SQL Loader. I got a table or view does not exist error.

我尝试向我的普通用户授予更多权限，但这没有用.因此，我放弃了SQL Loader并以sysdba的身份运行.有效！这样做不是一件好事，但它是为测试目的而开发的仅开发系统，因此，我不在乎.

I tried granting more permissions to my regular user, but it didn't work. So, I gave up and ran SQL Loader as sysdba. It worked! It is not a good thing to do, but it is a development only system made for testing purposes, so, I didn't care.

推荐答案

我无法重复您的循环同义词链错误，但似乎控制文件需要一些工作，至少对于我的环境而言.

I could not repeat your looping synonym chain error, but it appears the control file needed a bit of work, at least for my environment.

这样修改就可以使您的示例生效:

I was able to get your example to work by modifying it thusly:

load data
infile *
append
into table test
fields terminated by "," optionally enclosed by '"'
(name,
 id
)
begindata
"GURRR",4567

这篇关于我使用sqlldr时收到ORA-01775:循环的同义词链错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！