RE: sql statement help

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 14 Feb 2016 07:24:24 -0500
Message-ID: <01b801d16722$a4011b60$ec035220$_at_rsiz.com>

In this thread you wrote:  

“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…

“

and  

suggested that you’d run the query on each of your databases and have someone cobble the data together from the separate results of output on each database.  

Is there no database under this generation the span of control of OEM where you can establish an account with read access to all_users on each of the databases?  

(By the way, you’ve been asked a question about all your databases. Do you expect this to be a unique event? If so, think again. You will NEED a centralized account with read access to metadata regarding all your databases to efficiently answer the questions that will come. Apart from that the DBA team should have such a database anyway. Since the late 1980’s I’ve called that the DBA data warehouse, typically having an account (aka user aka schema) for each database. Without that sort of repository, how will you track things like important queries and growth separated from the user data so you can do things like track growth and usage level?)  

With regard to “that db_link in all of my databases? Can’t do that…” a db_link for each of your user databases could be in the repository database, so you pull metadata from the various and sundry user databases into the repository database as opposed to creating something in each of your user databases.  

Now Anthony has been kind enough to cobble together a quick and dirty hardwired query that matches your specification. Nothing I write here should be interpreted as a slam on Anthony for being nice and actually answering the question you asked.  

That should work and some poor schlub can be left with the job of creating another quick and dirty way to cobble the output together.  

And a job in one, three, six months, or a year I predict you will have is: “Give me a comparison of that list of missing users and show me how it compares to last time. By the way, add ops$joe_schblotnick to the list.”  

Of course we do have database technology.  

But seriously, if this is the only question you plan to ever answer about multiple databases, run the nice hardwired query Anthony wrote and be done with it.  

Otherwise, I’d suggest you create:  

1. A centralized database for the dbas in which to collect metadata. Call this DBA_WH for shorthand. Control access to DBA_WH at least as well as you control the ability to run queries on all your databases through OEM.
2. A public dblink to each user database in DBA_WH.
3. A user for each database tracked in DBA_WH, named boringly after the database name in some way that pleases you.
4. A master schema to hold things such as a table of all the databases of interest and the dblink to each.
5. A table listing at least the database name and dblink of each database of interest. You probably also want the dbid, but I’ll leave that out for now.

That is all infrastructure in general. Given that, then specifically for your current task:  

6) A table with a row for each “user of interest”. Insert your five user names into that table. For brevity here, call it users.

7) A table to insert the results into. Let’s call it user_db_id.  

You probably want columns username (not null), dbid (not null), databasename (not null), as_of_date (not null), user_id, created. At least. But I’m leaving out dbid for now.  

THEN you generate the queries to run on the repository. A single one of the queries might look like this (remembering these hardwired values are generated and could be fed with a bind variable for the database name, but it will be difficult to do that with the dblink name, so you’re going to have unique sqls anyway.)  

insert into user_db_id

   select u.username,'RSIZ',sysdate,a.user_id, a.created

      from users_of_interest u, all_users_at_rsiz a

      where u.username = a.username(+)    

So you run (_at_filename) the file containing the 150 of those you generated and each will have a different value for ‘RSIZ’ and ‘_at_rsiz’ that you got from your table of databasenames and dblinks. Then you commit and run:  

select username,databasename

  from user_db_id where user_id is null

  order by username,databasename  

And you have your report. I leave it to the user to figure out useful queries that are likely to erupt over time answering questions related to the as_of_date and the created columns.  

Of course this is all a lot more work to do the first time you need to answer a question about multiple databases.  

I’d have to ask Peter or Kellyn if there is a feature in OEM or the cloudy version thereof to run a query (in particular the insert) on one of your databases substituting the dblink target for each of your databases. But whether you can do this all in OEM or have to use some generate sql scripts to produce your job, I think will be doing a useful think to generate an automated result instead of using automation to generate many reports from which to generate the final report.  

Except if you live in a world I’ve never seen where this will be the only time you answer a question like this. Then go ahead with the q&d.  

I didn’t know how to say this any shorter, and I almost showed you how to do it all in PL/SQL, which is where you should bundle up all this stuff anyway. Likely what you really should keep is a period list of all the users with access to each of your databases and a bit more complication of the data model to easily get a lot of answers down the road.  

mwf      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Jones Sent: Friday, February 12, 2016 2:43 PM
To: anthony Sanchez; andrew.kerber_at_gmail.com; MJ Chicago Cc: oracle-l
Subject: RE: sql statement help  

Thank you Anthony, that works perfect!  

John  

From: anthony Sanchez [mailto:anthonycsanchez_at_gmail.com] Sent: Friday, February 12, 2016 2:30 PM
To: andrew.kerber_at_gmail.com; MJ Chicago Cc: John Jones; oracle-l
Subject: Re: sql statement help  

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] 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 Sun Feb 14 2016 - 13:24:24 CET