Fetching user info from many databases at one go

oracleoracle-11g-r2oracle-12c

I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment.

All the schema information such as username, roles assigned and is to be dumped in one place from where it will be used for other purposes.

Is it feasible? I can provide more info if this question is sounding vague.

Are there any utilities to perform this?

Best Answer

As already said, this is really easy to script e.g using sqlplus + shell scripts.

Since you mentioned Enterprise Manager. Yes, it is possible through EM, but you need to set the preferred credentials for the databases and hosts. Once you have done that, you can execute an SQL on databases, or a group of databases. For example, I created a group called sandbox_databases in EM, added 3 databases in this group, then ran the same query for all of them:

$ emcli login -username=sysman
$ emcli execute_sql -sql="select count(*) from dba_users" -targets="sandbox_databases:group"

And the output is:

*******************************************************************************
* Target: XXXXX1:oracle_database
* Execution Status: Succeeded
*******************************************************************************

  COUNT(*)
----------
         9


*******************************************************************************
* Target: XXXXX2:oracle_database
* Execution Status: Succeeded
*******************************************************************************

  COUNT(*)
----------
        12


*******************************************************************************
* Target: XXXXX3:oracle_database
* Execution Status: Succeeded
*******************************************************************************

  COUNT(*)
----------
        10


*******************************************************************************
* Execution Summary
*     Targets Succeeded: 3
*         XXXXX1:oracle_database,XXXXX2:oracle_database,XXXXX3:oracle_database
*     Targets Failed: 0
*******************************************************************************
Related Question