RE: tables with no synonyms

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 7 Feb 2007 15:24:15 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08B05@EXCNYSM0A1AJ.nysemail.nyenet>

Joe,

Try this:

select table_name
from dba_tables
where owner='USER_A'
  and not exists(
   select 1 from dba_synonyms dbas
    where dbas.owner='USER_B'

	and dbas.table_owner = dba_tables.owner
	and dbas.table_name = dba_tables.table_name)

Tom

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Wednesday, February 07, 2007 3:16 PM To: oracle-l_at_freelists.org
Subject: tables with no synonyms

I am using this query to find objects ( owned by User A) that have no private synonyms (for User B) created for other users:

select s.owner, s.synonym_name, s.table_owner, s.table_name,

o.object_name, 
o.object_type
from dba_synonyms s, dba_objects o

where s.table_name = o.object_name
and o.owner='NAME'

How can this be improved upon?

we use a statement such as this to create private synonyms.

create synonym B.synonyn_name for A.object_name;

thanks.

Get in the mood for Valentine's Day. View photos, recipes and more on your
Live.com page.
http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 07 2007 - 14:24:15 CST