ora-01775 Looping chain of synonyms

今天在查询表的时候遇到了Ora-01775错误，查看帮助后得到如下解释：

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;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13129975/viewspace-662184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13129975/viewspace-662184/