synonym

[Pieter_5]

Hi,

How can I list all the public synonyms which are available in my database?

[T G Manikandan]

select table_name,synonym_name,table_owner from dba_synonyms;

to query for particular user

select table_name,synonym_name,table_owner from dba_synonyms where owner='USERNAME';

[Jean-Luc Oudart]

I think :
where owner='PUBLIC'
for public synonyms

JL

[T G Manikandan · Accepted solution]

you can also query all_synonyms with OWNER as PUBLIC


Thanks

[T G Manikandan]

The best way would be

select sname,creator,tname from synonyms where stype='PUBLIC';

[Raynald Boucher]

Hello,

All above answers are correct somehow but...
some synonyms do not have privileges attached to them. For example, you may create a public synonym for a table but only grant access to a few users; this reduces the number of objects in the data dictionary.

For best results, as DBA, join views DBA_SYNONYMS and DBA_TAB_PRIVS.

Take care.
Ray