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@xxxxxxxxx> 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@xxxxxxxxx ;<emjay.mody@xxxxxxxxx>]
*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@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
