正德厚生,臻于至善

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;
/
赞(0) 打赏
未经允许不得转载:徐万新之路 » ORA-01775 Looping chain of synonyms
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏