I think something like this should work:
with worktab as (select 'user1' as un from dual union select 'user2' as un
from dual union select 'user3' as un from dual union select 'user4' as un
from dual union select 'user5' as un from dual)
select w.un from worktab w,
dba_users d
where w.un=d.username (+) and d.username is null
On Fri, Feb 12, 2016 at 1:30 PM, anthony Sanchez <anthonycsanchez@xxxxxxxxx>
wrote:
Its kind of quick and dirty but may work if you only care about those 5
users. You have to edit 2 places on each select statment.
SELECT 'DBSNMP' username,
DECODE ( (SELECT COUNT (*)
FROM all_users
WHERE username = 'DBSNMP'),
0, 'MISSING',
1, 'EXISTS')
user_exists_status
FROM DUAL
UNION
SELECT 'OPS$JOHN' username,
DECODE ( (SELECT COUNT (*)
FROM all_users
WHERE username = 'OPS$JOHN'),
0, 'MISSING',
1, 'EXISTS')
user_exists_status
FROM DUAL
UNION
SELECT 'OPS$DAVE' username,
DECODE ( (SELECT COUNT (*)
FROM all_users
WHERE username = 'OPS$DAVE'),
0, 'MISSING',
1, 'EXISTS')
user_exists_status
FROM DUAL
UNION
SELECT 'OPS$LEE' username,
DECODE ( (SELECT COUNT (*)
FROM all_users
WHERE username = 'OPS$LEE'),
0, 'MISSING',
1, 'EXISTS')
user_exists_status
FROM DUAL;
USERNAME USER_EXISTS_STATUS
-------- ------------------
DBSNMP EXISTS
OPS$DAVE MISSING
OPS$JOHN MISSING
OPS$LEE MISSING
4 rows selected.
On Fri, Feb 12, 2016 at 12:13 PM Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
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
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
