Re: sql statement help

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 12 Feb 2016 13:34:01 -0600
Message-ID: <CAJvnOJbVODTozjS=3y6z=94_9v9kkFKA-8XgTetidfRcfnnweA_at_mail.gmail.com>

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_at_gmail.com> 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_at_gmail.com>
> 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_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.'
>>
>

-- 
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:34:01 CET