Detecting Invalid Synonyms

Does anybody know how to detect invalid synonyms? (Does ORA-980 ring a bell?)

I'm starting off by:

1. unravelling a synonym [that points to a synonym [, that points to a synonym]...]
2. validating existence of the object at the end of the synonym chain

I'm not having much luck with #1. It seemed like a good use for CONNECT BY on the all_synonyms view, but it fails with "ORA-01437: cannot have join with CONNECT BY". Just for grins, I created a temp table from that view and it does pretty much what I need:

     SQL> create table goofy as select * from all_synonyms;

     SQL> select  synonym_name, table_owner, table_name
       2  from    goofy
       3  connect by prior table_name=synonym_name
       4* start   with synonym_name='FOO3'

     SYNONYM_NAME TABLE_OWNER  TABLE_NAME
     ------------ ------------ ------------
     FOO3         JEAN         FOO2
     FOO2         JEAN         FOO1
     FOO1         JEAN2        FOO

I then check all_objects to make sure 'FOO' really exists.

I say it does 'pretty much what I need' because if synonym jean.foo points to jean2.foo, CONNECT BY fails with ORA-1436 (circular reference). We, of course, have lots of synonyms that are the same name as a table owned by a user.

I'd like to avoid C code, if at all possible. Does anybody know a reliable way to unravel synonyms and detect invalid ones in SQL*Plus?

Thanks in advance,

  -jean

+-----------------------------------------------------------------------+


| Jean Anderson, DBA                       email:  jean_at_seismo.css.gov  |


| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |

| 10210 Campus Point Drive                 phone:  (619)458-2727        |

| San Diego, CA  92121                       fax:  (619)458-4993        |

 +-----------------------------------------------------------------------+