Looping chain of synonyms: how to identify? 1

[SJSFoxPro]

Does anyone have a cleverly written query against the data dictionary views (or any other method) to identify a looping chain of synonyms?

Apparently, synonyms have been created that point to the same object and to each other and have, in turn, created a circular reference. (No punn intended ;-) )

 

[SantaMufasa]

SJS,

First, here are some sample data:

SQL> create synonym s1 for s2;

Synonym created.

SQL> create synonym s2 for s3;

Synonym created.

SQL> create synonym s3 for s4;

Synonym created.

SQL> create synonym s4 for s1;

Synonym created.

Notice the "recursive" nature of the synonym chain from "S1 down to S4, then back again to S1". Here then is code to display the relationships amongst the synonyms, including code to "trap and display" the recursion:

set serveroutput on format wrap
prompt
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 user_synonyms
		 connect by prior synonym_name = table_name
		   start with synonym_name = 'S3') loop
		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;
/

Synonym Hierarchy
----------------------------------------
   S3
      S2
         S1
            S4
               S3
                  S2
                     S1
Error: connect-by loop following "S1"

PL/SQL procedure successfully completed.

Let us know if this satisfies your need.
[/code]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

Hi Dave,

I knew you'd come through on this one!

I did find my problem yesterday, however, it was more of a manual search to narrow down the list of tables that were involved and then a close look at each one (a little time consuming). For future use, I want this in my "tool kit" to locate the culprit quicker!

If I don't know any of the synonyms involved in the loop and I want to query all synonyms to see if any cause recursion, do I just randomly select a synonym name to use in the starts with?

Thanks again,
Susan

 

[SantaMufasa]

Yep. You can "START WITH" any synonym you wish and it should show the hierarchy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.