Re: tables with no synonyms

Another type of improvement would be to have the query build your CREATE PUBLIC SYNONYM statements for you instead of just selecting the names of the objects... something like this (this is a quick answer so please forgive any typos or minor syntax errors...):

SELECT 'CREATE PUBLIC SYNONYM ' || a.object_name || ' FOR ' || a.owner || '.' || a.object_name || ';'
FROM... Then you could look over the results, mouse-copy them, and be done.

HTH
Tim

On 2/7/07, Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:
>
> Along with the several good answers to this thread, I'll point out a
> couple of "gotchas":
> The subject of the e-mail says "tables" but the original query was linking
> to DBA objects.
> The first question is: you are looking for missing synonyms for which
> object types? Synonyms for views, tables, sequences, procedures, functions,
> packages, materialized views?
> Second question is: are the synonyms supposed to have the same names as
> the objects they are pointing to?
>
> See the comment lines in this query:
>
> column object_name format a30
> select
> a.owner, a.object_name, a.object_type
> from
> dba_objects a
> where
> a.owner = '&BASE_OBJECT_SCHEMA'
> -- choose the appropriate object types for the "base object"
> and a.object_type in
> ('FUNCTION', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE',
> 'SEQUENCE', 'TABLE', 'VIEW')
> and not exists
> (select null from dba_synonyms b
> where
> b.owner = '&SYNONYM_SCHEMA'
> and a.owner = b.table_owner
> and a.object_name = b.table_name
> -- include this line if the synonym must have the same name as
> the
> -- "base object"
> and b.table_name = b.synonym_name
> )
> order by a.owner, a.object_type, a.object_name ;
> ------------------------------
> *De :* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> *De la part de* Nigel Thomas
> *Envoyé :* mercredi, 7. février 2007 12:56
> *À :* oracle-l
> *Objet :* 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