SA-DBA

Jul 10 2009   12:04AM GMT

Grid Control Job to Excel Worksheet

Richard Evans Richard Evans Profile: Richard Evans

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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: