job

Grid Control Job to Excel Worksheet

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.