Script to query multiple Oracle servers & instances

Question

If I have a list of either database servers, or database instances, is there any way I can create and run a single script-query that would give me a list of all users in all databases? Are there any tools that would do this? Perhaps an OEM based enhancement?

Ideally I'd like to just point the script-query at a tnsnames file, with the appropriate username/password and let it run from there. We have Oracle v10.2 environment in AIX and Linux & windows.The database are not Oracle GRID managed.

Thanks

Yes, read in the list, connect to each database individually and write out the list to another file... — Ben
– Ben, Commented May 16, 2013 at 15:40
Yes, I can connect to oracle via powershell and execute the query but not sure about reading the list and querying each database. — user362283
– user362283, Commented May 16, 2013 at 16:04
Which part of "reading the list" do you need help with - getting the file content, or parsing it? Once that's done, all you need is a loop to iterate through each server. You will get much more help on SO if you show put in some effort & need help getting past an obstacle (show your code) as opposed to asking someone to write the whole thing for you. — alroc
– alroc, Commented May 19, 2013 at 12:22

Dave · Accepted Answer · 2013-05-16 16:08:19Z

1

It might not be what you are after but you could create a set of dblinks from one db to all the others and then run a simple select statement across the dblinks

SELECT username from all_users@dblink1
UNION 
SELECT username from all_users@dblink2
UNION
SELECT username from all_users@dblink3

etc.

If you had the DBLinks in place you could even create the select dynamically

answered May 16, 2013 at 16:08

Dave

1513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Script to query multiple Oracle servers & instances

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related