DBA_SYNONYMS

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

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');

USER_SYNONYMS

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;