Executing SQL Scripts Against Multiple Database Targets Using 10G OEM Grid Control R2
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!