topic Re: synonym in Operating System - HP-UX

synonym

Pieter_5 — Thu, 18 Sep 2003 10:53:48 GMT

Hi,

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

Re: synonym

T G Manikandan — Thu, 18 Sep 2003 10:57:30 GMT

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

Re: synonym

Jean-Luc Oudart — Thu, 18 Sep 2003 11:02:12 GMT

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

JL

Re: synonym

T G Manikandan — Thu, 18 Sep 2003 11:13:14 GMT

you can also query all_synonyms with OWNER as PUBLIC

Thanks

Re: synonym

T G Manikandan — Thu, 18 Sep 2003 11:17:40 GMT

The best way would be

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

Re: synonym

Raynald Boucher — Fri, 19 Sep 2003 11:13:17 GMT

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