Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » Executing SQL Scripts Against Multiple Database Targets Using 10G OEM Grid Control R2
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3620
 

Executing SQL Scripts Against Multiple Database Targets Using 10G OEM Grid Control R2 Executing SQL Scripts Against Multiple Database Targets Using 10G OEM Grid Control R2

Last week, we discussed 10G Grid Control R2’s feature that allows us to run host commands against multiple targets at the same time. 10G Grid Control R2 also provides an easy-to-use panel that allows administrators to run SQL scripts against multiple database targets simultaneously. Just like its host command counterpart, all we need to do is code up a SQL statement, choose a set of database targets, run the SQL against the multiple targets and review the output. Simple as can be.

Navigating to the Execute SQL Statement Home Page
Let's begin by activating 10G Grid Control R2 and navigating to the Execute SQL Statement Home page. We accomplish that by selecting the 'Targets' tab at the top of 10G Grid Control R2's Home Page. 10G Grid Control R2 displays the Hosts Home Page. We continue our navigation by selecting the 'Databases' sub-tab on the top of the Hosts Home page. 10G Grid Control R2 responds by displaying the Database Target Home page. At the bottom left of the database target home page, 10G Grid Control R2 provides a link to let us access the Execute SQL Statement Home page.

Execute SQL Statement Home Page
Let's spend a couple of minutes reviewing this page. You'll notice that it is almost an exact duplicate of the Execute Host Command Home page that I discussed in my last blog. If we start at the top of the Execute SQL Statement Home page, you'll notice that I have already entered my first SQL statement:

'SELECT NAME FROM DBA_TABLESPACES'

in the SQL statement input box. I have purposely chosen the incorrect column name 'NAME' instead of the correct column name 'TABLESPACE_NAME' in the SQL statement. This is to show you the error processing that occurs when you submit an incorrect SQL statement for execution.

Look at the text above the SQL statement's input box. It states that you can execute either SQL or PL/SQL. What is more important to note are the words "Any database changes resulting from the commands must be rolled back manually if the changes are not desired." This is NOT a sissy SQL*PLUS session, so you can't rollback or undo unwanted data changes with the ROLLBACK SQL statement. The data changes you will make will be permanent. So, watch what you execute on this panel.

Like its Execute Host Command counterpart, the Execute SQL Statement Home page also allows users to load a script from their client or from a monitored host target by clicking on the 'Load OS Script" navigation button. 10G Grid Control R2 also allows users to maintain a library of commonly used SQL and PL/SQL scripts in the Enterprise Manager Repository. We navigate to the library of stored scripts by selecting the 'Load from Job Library' buttons that 10G Grid Control R2 displays at the top and bottom of the Execute SQL Statement Home page. Since this is a very new installation of 10G Grid Control R2, we don't have any scripts stored in the library. But as I previously stated in my blog on Host script execution, I expect us to take advantage of this feature very soon.

Adding Targets
Now that I have entered my incorrect 'SELECT NAME FROM DBA_TABLESPACES' command in the input box, let's add some database targets to execute it on. I do this by clicking on the 'ADD' button that is displayed on the lower right hand side of the Execute SQL Statement Home page. 10G Grid Control R2 responds by displaying the Add Targets Pop-up Window. I am able to choose one, or all, of the monitored database targets. I select them by clicking on the 'Select' check box at the beginning of each database target's row.
Once I have selected the desired database targets, I click on the 'Select' navigation button that is displayed on the lower right hand corner of the pop-up. 10G Grid Control R2 returns us back to the Execute SQL Statement Home page.

Executing the SQL Statement
Our next step is to execute the statement by clicking on the 'Execute' navigation button. 10G Grid Control R2 displays the Processing: Executing SQL Statement status page. The numbers will increment as the command is executed on the different database targets. When the statement is complete, 10G Grid Control R2 returns us to the Execute SQL Statement Home page.

Our Error
Notice that we have a series of red 'X's displayed at the bottom of the page. 10G Grid Control R2 is politely telling us that it had a problem processing our statement. We can click on the "Show Details" link on the left side of the page to review the error messages contained in the Execution Results page for each of the database servers. I chose the 'Show Details' link from the first server. The error messages displayed on the first server's Execution Results page states that the 'NAME' column was not contained in the DBA_TABLESPACES dictionary table.

Correcting and Rerunning the Statement
I corrected the error by changing 'NAME' to 'TABLESPACE_NAME' in the SQL statement input box. I clicked on the 'Execute' navigation button to execute the corrected statement. 10G Grid Control R2 displays the processing screen again. After processing is complete, 10G Grid Control R2 returns us to the Execute SQL Statement Home page. The page looks a little different since the last time we viewed it. No more red 'X's.

SQL Statement Command Output
The Execute SQL Statement Home page now displays the output from the SQL statement. Each row in the output table contains a single database's output. We can expand or collapse the output displayed. This allows us to easily navigate through the different database target's output. I clicked on the 'Complete Execution Results' navigation button to view the entire result set from the first database target. We can also save the output to an HTML file by clicking on the 'Save Results' navigation button.

SQL Execution History
We are also able to review previous SQL statement executions by clicking on the 'Execution History' button that is displayed on the top right hand side of the Execute SQL Statement Home page. The command execution output page allows us to load the results if we want to review them again, or load just the SQL statement/PL/SQL code to execute them to obtain more current information.


Summary

I hope you enjoyed my second blog of the New Year. In my next blog, I'll discuss the 10G R2 reporting feature available in Oracle's latest and greatest release of Enterprise Manager, 10G Grid Control R2.


Thanks for Reading!


Monday, January 16, 2006  |  Permalink |  Comments (0)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-01-14.3116717615/sbtrackback
 

Powered by Plone