Re: sql statement help

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 12 Feb 2016 13:13:00 -0600
Message-ID: <CAJvnOJZJzjK3wDQ8Zz23J2j1kwxMDXLG+aq7g1j1wBxZ7iPRPQ_at_mail.gmail.com>



Is the difficulty in designing the query or running it on 150 instances?

If you have OEM, there is a facility to run the same sql statement on multiple instances. If the problem is designing the query, several suggestions have been provided.

On Fri, Feb 12, 2016 at 1:07 PM, MJ Mody <emjay.mody_at_gmail.com> wrote:

> Apologies in advance for making assumptions. Curious as to how you're
> planning to run this on all your dbs?
>
> Try these:
> - select * from all_users where username in ('user1', 'user2', 'user3');
>
> - select count(1) from all_users where username in ('user1', 'user2',
> 'user3');
>
> - select vi.instance_name, au.username
> from v$instance vi, all_users au
> where au.username in ('user1', 'user2', 'user3');
>
>
>
> On 2/12/16 1:00 PM, John Jones wrote:
>
> Then I would have to have that db_link in all of my databases? Can’t do
> that…
>
>
>
> Thanks anyway
>
> John
>
>
>
> *From:* MJ Mody [mailto:emjay.mody_at_gmail.com <emjay.mody_at_gmail.com>]
> *Sent:* Friday, February 12, 2016 1:59 PM
> *To:* John Jones
> *Subject:* Re: sql statement help
>
>
>
> Not an sql expert and this is only an attempt:
>
> select * from all_users_at_db_link
> where username in (select * from all_users where username in ('user1',
> 'user2', 'user3'))
> union all
> ..
> ..
> ..
> order by username asc;
>
>
> On 2/12/16 11:58 AM, John Jones wrote:
>
> I have about 150 databases that I need to run a query in to find out if
> certain users exists or better yet do not exists.
>
>
>
> I have 5 users ops$john, ops$dave, ops$mark, ops$ken, and ops$lee. I need
> to run a SQL statement that would let me know if any or all of those users
> do not exists in the all_users table. How do I write a SQL statement that
> would allow me to pass that list of users and list those that are not there.
>
>
>
> My boss is asking for a report like this and I have no idea how to write
> such a thing. I have not written SQL in years and stumbling to provide an
> answer.
>
>
>
> Any SQL guru’s have suggestions?
>
>
>
> John
>
>
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 12 2016 - 20:13:00 CET

Original text of this message