ORA-01775 Looping chain of synonyms

原文链接：http://blog.itpub.net/13129975/viewspace-662184/

今天在查询表的时候遇到了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;
/