Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

synonym

SOLVED
Go to solution
Pieter_5
Advisor

synonym

Hi,

How can I list all the public synonyms which are available in my database?
5 REPLIES
T G Manikandan
Honored Contributor

Re: synonym

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
Honored Contributor

Re: synonym

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

JL
fiat lux
T G Manikandan
Honored Contributor
Solution

Re: synonym

you can also query all_synonyms with OWNER as PUBLIC


Thanks
T G Manikandan
Honored Contributor

Re: synonym

The best way would be

select sname,creator,tname from synonyms where stype='PUBLIC';
Raynald Boucher
Super Advisor

Re: synonym

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