All Synonyms in the database:
select o.object_type,s.table_name, s.synonym_name,s.table_owner,s.owner,O.STATUS,S.DB_LINK
from dba_synonyms s,dba_objects o
where o.object_name=s.table_name
and o.owner=s.table_owner
and s.owner='PUBLIC'
and S.TABLE_OWNER not in('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN','DBSNMP','SYSMAN','OLAPSYS');
All Synonyms accessible by user:
select o.object_type,s.table_name, s.synonym_name,s.table_owner,s.owner,O.STATUS,S.DB_LINK
from all_synonyms s,all_objects o
where o.object_name=s.table_name
and o.owner=s.table_owner
and s.owner='PUBLIC'
and S.TABLE_OWNER not in('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN','DBSNMP','SYSMAN','OLAPSYS');
The User’s Private Synonym:
select o.object_type,s.table_name, s.synonym_name,s.table_owner,O.STATUS,S.DB_LINK
from user_synonyms s,user_objects o
where o.object_name=s.table_name;