Re: ALL_SYNONYMS versus USER_SYNONYMS

From: vijay sehgal <vijaysehgal21_at_gmail.com>
Date: Mon, 15 Sep 2014 16:15:30 +0530
Message-ID: <CALQThVe7gAwBC59MVRpfWTP_++ROSM5nQ3iOjrLv_mef4eSn1w_at_mail.gmail.com>

is the application connecting to some schema and executing queries where the schema/user would have got grant to synonym and hence all_ and not user_ ?

Regards,
Vijay Sehgal.

On Mon, Sep 15, 2014 at 2:25 PM, De DBA <dedba_at_tpg.com.au> wrote:

> I think that user_synonyms won't list public synonyms, whereas
> all_synonyms should...
>
> Cheers,
> Tony
>
> On 15/09/14 18:39, Chitale, Hemant K wrote:
>
> I have developers who prefer to use the ALL_% views (e.g. ALL_TABLES)
> even when I tell them to use the USER_% views (USER_TABLES).
>
> Must be something in their prior experiences that “taught” them to use the
> ALL_% views !
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *David Fitzjarrell
> *Sent:* Friday, September 12, 2014 11:07 PM
> *To:* lyallbarbour_at_sanfranmail.com; mohamed.houri_at_gmail.com; ORACLE-L
> *Subject:* Re: ALL_SYNONYMS versus USER_SYNONYMS
>
>
>
> In the absence of the qualifier "where owner=user" ALL_SYNONYMS can
> contain more synonyms than USER_SYNONYMS however the queries you post are
> equivalent. I second Lyall's question of why does the app 'need' to know
> about synonyms? This should be a configuration step prior to releasing the
> application to production; there should be no need for such a query to be
> run.
>
>
>
> It sounds like either the developers are misinformed or, well, arrogant.
> I would hope it is due to misinformation.
>
>
>
> David Fitzjarrell
>
> Principal author, "Oracle Exadata Survival Guide"
>
>
>
> On Friday, September 12, 2014 4:02 AM, Lyall personal <
> lyallbarbour_at_sanfranmail.com> wrote:
>
>
>
> You could give your developers the code for those two views. user_synonyms
> text does what they are doing in the where clause. At least looks like that
> to me.
>
> Strange query for an "application" to run. Why does the app need to know
> if synonyms exist?
>
>
>
> Sent from my BlackBerry 10 smartphone.
>
> *From: *Mohamed Houri
>
> *Sent: *Friday, September 12, 2014 5:47 AM
>
> *To: *ORACLE-L
>
> *Reply To: *mohamed.houri_at_gmail.com
>
> *Subject: *ALL_SYNONYMS versus USER_SYNONYMS
>
>
>
> Dear list,
>
>
>
> I was tuning an application wide performance issue via an AWR report when
> I found a SQL consuming a huge number of logical I/O and executed several
> times. This SQL looks like:
>
> SELECT COUNT(1)
>
> FROM DUAL
>
> WHERE EXISTS
>
> (SELECT 1 FROM ALL_SYNONYMS WHERE SYNONYM_NAME = :B1 AND OWNER = USER
>
> );
>
>
>
> After a couple of minutes of discussion with developers, they refused to
> get rid of this part of the code which seems to me useless. Then, in a
> second tentative, I suggested them to replace the above code with the
> following one:
>
> SELECT COUNT(1)
>
> FROM DUAL
>
> WHERE EXISTS
>
> (SELECT 1 FROM USER_SYNONYMS WHERE SYNONYM_NAME = :B1
>
> );
>
>
>
> They refused again saying that it will not give the same results.
>
>
>
> Can someone let me know a situation where this result difference is
> possible?
>
>
>
> SQL> SELECT count(1) FROM ALL_SYNONYMS where OWNER = USER;
>
>
>
> COUNT(1)
>
> ----------
>
> 405
>
>
>
> SQL> SELECT count(1) FROM USER_SYNONYMS;
>
>
>
> COUNT(1)
>
> ----------
>
> 405
>
>
>
> Thanks in advance
>
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Member of Oraworld-team <http://www.oraworld-team.com/>
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>
>
>

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

Received on Mon Sep 15 2014 - 12:45:30 CEST