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