oracle 同义词循环连,ORA-01775: 同义词的循环链问题

1、先查有没有循环的同义词：

select * from dba_synonyms where

table_owner='test' and synonym_name

<>table_name;没有记录

2、再查同义词没有对象的数据库对象：

select * from dba_synonyms where

table_owner='test'

and sysnonym_name in(select a.synonym_name from dba_synonyms

a

where a.table_owner='test'

minus

select object_name from user_objects

)

3、把查询出来的结果进行查询表

select * from DRILL_PRESON

如果该同义词没有对应的对象，就会报ora_01775的错误

4、删除这个同义词：

drop public synonym DRILL_PRESON

也有网友发文

ORA-01775 looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was

defined that referred to itself. For example, the following

definitions are circular:

CREATE SYNONYM s1 for s2

CREATE SYNONYM s2 for s3

CREATE SYNONYM s3 for s1

Action: Change one synonym definition so that it applies to a base

table or view and retry the operation

SELECT owner, synonym_name, connect_by_iscycle cycle

FROM dba_synonyms

WHERE

connect_by_iscycle > 0

CONNECT BY nocycle PRIOR

table_name = synonym_name

AND PRIOR

table_owner = owner

UNION ALL

SELECT s.owner, s.synonym_name,

1

FROM dba_synonyms s

WHERE s.owner

= 'PUBLIC'

AND

s.table_name = s.synonym_name

AND NOT

EXISTS (SELECT 1

FROM dba_objects o

WHERE

s.table_owner = o.owner

AND

s.table_name = o.object_name

AND

o.object_type != 'SYNONYM');

DECLARE

connect_by_loop

EXCEPTION;

PRAGMA

EXCEPTION_INIT(connect_by_loop, -1436);

hold_prev_synonym_name

user_synonyms.synonym_name%TYPE;

BEGIN

dbms_output.put_line('Synonym

Hierarchy');

dbms_output.put_line('----------------------------------------');

FOR x IN (SELECT lpad(' ',

LEVEL * 3) || synonym_name a, synonym_name b

FROM dba_synonyms

CONNECT BY PRIOR synonym_name =

table_name

START WITH

synonym_name = &synonym_name)

LOOP

EXIT WHEN x.a IS NULL;

hold_prev_synonym_name :=

x.b;

dbms_output.put_line(x.a);

END LOOP;

EXCEPTION

WHEN connect_by_loop THEN

dbms_output.put_line('Error:

connect-by loop following "' ||

hold_prev_synonym_name || '"');

END;

/