RE: sql statement help

From: John Jones <john.jones_at_duke.edu>
Date: Fri, 12 Feb 2016 19:11:13 +0000
Message-ID: <BN3PR0501MB120330D688C20DC6DB172EFCEFA90_at_BN3PR0501MB1203.namprd05.prod.outlook.com>

No problem. We use OEM to submit the job to each database, then one of our guys wrote report that can pull all that output together. I just need to get the SQL that would show me what id's from that list do not exist a database

From: MJ Mody [mailto:emjay.mody_at_gmail.com] Sent: Friday, February 12, 2016 2:08 PM
To: John Jones; oracle-l
Subject: Re: sql statement help

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] 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

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

Received on Fri Feb 12 2016 - 20:11:13 CET