Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tables with no synonyms

RE: tables with no synonyms

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 8 Feb 2007 08:58:35 -0500
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B02929D6A@jax-mbh-01.jax.crowley.com>


Untested, just out of my head, Try something like:  

Select 'create synonym '||object_name||' for A.'||object_name||';' from dba_objects where owner = 'A'

Minus

Select 'create synonym'||synonym_name||' for A.||synonym_name||';' from dba_synonyms where owner = 'B';    

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nigel Thomas Sent: Wednesday, February 07, 2007 3:56 PM To: oracle-l
Subject: Fw: tables with no synonyms  

>How can this query to find objects ( owned by User A) that have no
>private synonyms (for User B) be improved upon
 

Joe  

Assuming I've understood the question correctly, you can just take the objects (including program units) that are owned by user A, and subtract (MINUS) the synonyms owned by B that reference those objects in A.  

select object_name, owner from dba_objects where owner = 'A' minus
select table_name,table_owner from dba_synonyms where owner = 'B' /  

Regards Nigel    

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 08 2007 - 07:58:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US