Re: sql statement help

From: anthony Sanchez <anthonycsanchez_at_gmail.com>
Date: Fri, 12 Feb 2016 19:30:28 +0000
Message-ID: <CAEwv4fFG-8hmoDRc_rq0_h=mxKQ7Ry2uDpEk4evxHb4Z1Myc9g_at_mail.gmail.com>

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.'
>

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

Received on Fri Feb 12 2016 - 20:30:28 CET