RE: tables with no synonyms
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Feb 2007 09:04:24 -0500
Geez, It's not just a Science, it's an art!
Jacques does have a point. I would suggest that if you only want
tables, user dba_tables... etc. I would also point out that user 'A'
run the code and query all_objects for the top and user_synonyms for the
bottom...
e.g.:
Select 'create synonym '||table_name||' for A.'||table_name||';' from
all_tables where owner = 'A'
Minus
Select 'create synonym'||synonym_name||' for A.||synonym_name||';' from
user_synonyms;
Joel Patterson
Database Administrator
joel.patterson@xxxxxxxxxxx
x72546
904 727-2546
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Joel.Patterson@xxxxxxxxxxx
Sent: Thursday, February 08, 2007 8:59 AM
To: nigel_cl_thomas@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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:
