Often times I’m asked to run a report that requires querying multiple databases. I suppose I could just setup DB Links and use a UNION ALL but why, when I have Grid Control?
First thing I do is setup a SQL Script job in Grid Control that looks like this. In addition to the user information, this query will get the DB Name so I know the user-DB relationship.
WHENEVER SQLERROR EXIT FAILURE;
set head off
set lines 210
column output format a140
select (select name from v$database)||’,’||username||’,’||account_status||’,’||created||’,’||profile as output from dba_users;
Make sure you run this job as SYSDBA so you have the correct privileges to query dba_users
Now that you have the results you can query them from SYSMAN’s table…
select output from MGMT$JOB_STEP_HISTORY where job_name = ‘TEST111′;
Paste that output into Excel and you’ll end up with something like this:
Now use the Text to Columns function in Excel to migrate the data to individual columns.
The final output!
This is a great way to produce quick reports that span multiple databases.