RE: tables with no synonyms

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@xxxxxxxxxxx 
x72546 
904  727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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

Other related posts: