VIEW: SYS._ALL_SYNONYMS_TREE

Source

View Text - Preformatted

select s.syn_id, s.syn_owner, s.syn_synonym_name, s.syn_table_owner,
       s.syn_table_name, s.syn_db_link, s.origin_con_id
from sys."_ALL_SYNONYMS_FOR_SYNONYMS" s
/* user has any privs on ultimate base object */
start with exists (
  select null
  from sys."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" sa
  where s.base_syn_id = sa.syn_id and s.origin_con_id = sa.origin_con_id
  )
connect by nocycle prior s.syn_id = s.base_syn_id and
                   prior s.origin_con_id = s.origin_con_id

View Text - HTML Formatted

SELECT S.SYN_ID
, S.SYN_OWNER
, S.SYN_SYNONYM_NAME
, S.SYN_TABLE_OWNER
, S.SYN_TABLE_NAME
, S.SYN_DB_LINK
, S.ORIGIN_CON_ID FROM SYS."_ALL_SYNONYMS_FOR_SYNONYMS" S /* USER HAS ANY PRIVS ON ULTIMATE BASE OBJECT */ START WITH EXISTS ( SELECT NULL
FROM SYS."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" SA
WHERE S.BASE_SYN_ID = SA.SYN_ID
AND S.ORIGIN_CON_ID = SA.ORIGIN_CON_ID ) CONNECT BY NOCYCLE PRIOR S.SYN_ID = S.BASE_SYN_ID AND PRIOR S.ORIGIN_CON_ID = S.ORIGIN_CON_ID