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@rsiz a
where u.username = a.username(+)
So you run (@filename) the file containing the 150 of those you generated and
each will have a different value for ‘RSIZ’ and ‘@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of John Jones
Sent: Friday, February 12, 2016 2:43 PM
To: anthony Sanchez; andrew.kerber@xxxxxxxxx; MJ Chicago
Cc: oracle-l
Subject: RE: sql statement help
Thank you Anthony, that works perfect!
John
From: anthony Sanchez [mailto:anthonycsanchez@xxxxxxxxx] ;
Sent: Friday, February 12, 2016 2:30 PM
To: andrew.kerber@xxxxxxxxx; 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@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] ;
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.'
