Oracle
Enterprise Manager Administrator's Guide
Release 1.6 A63731-01 |
|
This chapter describes how to use the SQL Worksheet, which is part of Oracle Enterprise Manager. It explains how to:
The SQL Worksheet allows you to enter, edit, and execute
SQL and PL/SQL code. You can also execute the Server Manager DBA commands
described in Appendix B, DBA Command Reference.
You can also run scripts from the SQL Worksheet. The SQL Worksheet maintains
a history of the commands you have issued, so you can easily retrieve and
re-execute previous commands.
You can start a SQL Worksheet from the Enterprise Manager
Console window by choosing SQL Worksheet from the Tools menu. The new worksheet
is connected to the database you have selected in the Navigator tree list
or in the Map window.
If you start the SQL Worksheet from the Windows Program Manager,
a database Login dialog box appears.
If you connect as INTERNAL to a database that is currently
shut down, the following error message appears in the SQL Worksheet output
pane upon startup: Cannot obtain ORACLE version number. This message is
caused by a minor system interface discrepancy and will not affect SQL
Worksheet operation or the database connection.
A SQL Worksheet window (Figure 13-1) consists of the following elements:
The following figure illustrates a SQL Worksheet window.
The SQL Worksheet window is described below:
Upper window pane. Displays recently executed commands and output. SQL Worksheet stores the last 32 KB of output for display in the output pane. The actual amount stored varies depending upon the amount of system memory available.
Lower window pane. Enter and edit commands in the input pane.
Adjusts the relative sizes of the panes. Drag the split bar vertically to resize the panes.
Icon provides access to the Save Output As menu option.
Provides access to the following operations:
Imports the contents of an existing SQL script.
Saves the contents of the input pane to a file
Executes the command in the input pane. See Executing
Commands in the SQL Worksheet on page 13-8 for information about
executing commands in a SQL Worksheet.
Displays the Command History dialog box, which displays previously
executed commands. See Using the Command
History on page 13-12 for information about using the command history.
The File menu items are described below:
Displays the Connect dialog box to connect to an instance. To connect to multiple databases concurrently, you need to launch the application multiple times from the console.
Clears the contents of the input pane. You are prompted to save your existing work.
Displays the Open Worksheet dialog box to allows you to open an existing. SQL or ASCII text file. The contents of the file appear in the input pane.
Saves the contents of the input pane to the current file.
Saves the contents of the input pane to a file other than the one that is currently open.
Prints the contents of the input pane.
Saves the contents of the output pane to a file.
Prints the contents of the output pane.
Displays the Print Setup dialog box allowing you to set print parameters and choose a local printer.
The Edit menu items are described below:
Cuts selected text.
Copies selected text.
Pastes selected text at the location of the cursor.
Selects all the text in a document or selects all objects in a list.
Clears all text in a window pane.
The Worksheet menu items are described below:
Executes the commands in the input pane. Equivalent to the
Execute button or the Enter key.
See Executing Commands in the SQL Worksheet on page 13-8 for information about executing commands in a SQL Worksheet.
Allows you to select and execute a script. See Running Scripts from the SQL Worksheet on page 13-15 for information about running scripts from a SQL Worksheet.
Writes output to a specified file. See Saving Your Work on page 13-14 for information about spooling the output of a SQL Worksheet.
Displays the Command History dialog box, which displays previously executed commands. Equivalent to the Command History button. See Using the Command History on page 13-12 for information about using the command history.
Retrieves the previous command from the command history and
enters it in the input pane. The retrieved command replaces the entire
contents of the input pane.
You can execute Previous Command repeatedly to work back through the command history. Previous Command is unavailable if you have currently retrieved the oldest command from the command history.
Retrieves the next command from the command history and enters
it in the input pane. The retrieved command replaces the entire contents
of the input pane.
Next Command is unavailable if you have currently retrieved
the most recent command from the command history.
Use the keyboard shortcuts to quickly access and execute
commands from the command history.
Enterprise Manager executes commands you have entered in
the input pane of a SQL Worksheet.
You can enter SQL, PL/SQL, and Server Manager DBA commands
in the input pane of a SQL Worksheet. A command entered in the input pane
can contain tabs or carriage returns.
All standard text editing capabilities are available in the
input pane of a SQL Worksheet. You can edit text in the input pane using
the mouse, keyboard functions, and the Edit menu. In addition, you can
select text from the output pane and paste it into the input pane.
To execute the command you have entered in the input pane,
click the Execute icon in the input pane toolbar. You can also execute
the command by choosing Execute from the Worksheet menu.
When you execute a command, the command and its output appear
in the output pane. However, if you set the parameter ECHO to off, only
the output of the command is displayed. For information about setting SQL
Worksheet parameters, see the SET command in Appendix
B, DBA Command Reference.
The following figure illustrates executing a SQL statement
in a worksheet.
After you execute a command, the command remains in the input
pane, but is selected, as shown in Figure 13-2,
"Executing a Command in the SQL Worksheet". You can then type in the
next command, and it replaces the previous command.
In a SQL Worksheet you can execute the Server Manager DBA commands described in Appendix B, "DBA Command Reference". Some of these commands behave differently when executed in a SQL Worksheet than when executed in line mode. These commands are summarized below:
Displays the Connect dialog box when you issue the CONNECT command without specifying complete connect information. For information about connecting to an instance of a database, see Connecting to an Instance on page 1-23.
If recovery is needed, displays the Recover dialog box when you issue the RECOVER command without arguments. Otherwise, an error message is generated.
The line mode command HOST is not available in the SQL Worksheet.
When you start a SQL Worksheet from the Enterprise Manager
console, you are automatically connected to the same database as the console.
You can change this connection by issuing the CONNECT command from the
input pane of the worksheet. The title of the SQL Worksheet window also
changes to reflect your new connection.
If you execute the CONNECT command from the input pane without
any arguments or select Change Database Connection from the File menu,
the Connect dialog box appears. You can use the Connect dialog box to specify
a new connection for that worksheet. For information about connecting to
an instance of a database, see Connecting
to an Instance on page 1-23. You can also specify the connection
using arguments in the CONNECT command. For a description of the CONNECT
command and its syntax, see Appendix B, "DBA
Command Reference".
When you issue the RECOVER command from a SQL Worksheet,
the Recovery dialog box appears, if recovery is needed. The following figure
illustrates the Recovery dialog box.
From the Recovery dialog box, you can apply the next log
file, apply all the log files, or cancel recovery. You can specify the
name of the log file to apply or apply the default.
A SQL Worksheet maintains a history of the last 20 command
executions you have issued in that worksheet. To bring up the Command History
dialog box, click Command History or choose Command History from the Worksheet
menu.
Command executions larger than 50K are not recorded in the
Command History.
The Command History dialog box contains a scrolling list
of the commands in the worksheet's command history. The following figure
illustrates the Command History dialog box.
The Command History dialog box is described below:
Displays an abbreviated list of the last 20 command executions. Explicit SQL commands associated with each command execution are displayed in the Command Text pane.
Displays all SQL commands associated with the selected entry in the Command List.
Closes the dialog box without retrieving any text.
Retrieves the selected item in the Command List command. See Retrieving a Selection from the Command History on page 13-13 for more information about retrieving commands.
Displays help information for the Command History dialog
box.
To retrieve a command from the Command History dialog box,
select the desired command execution entry in the Command List. All SQL
commands associated with this entry are displayed in the Command Text pane.
Click Get. The contents of the Command Text pane appear in the input pane
replacing any existing text.
You can also drag and drop commands directly into the input
pane by selecting an entry in the Command List and dropping it into the
input pane. The text associated with the entry appears in the input pane.
To retrieve only a portion of a command execution from the
Command Text pane, select the text you wish to retrieve and press Ctrl-C
(copy). Move the cursor to the worksheet's input pane, place the cursor
at the position where you want the text inserted and press Ctrl-V (paste).
If no text is selected in the input pane, the copied text
is inserted at the cursor position. If you have selected text in the input
pane, then the retrieved text replaces the selected text.
After you have retrieved text from the command history, you
can edit or execute the command, as desired.
You can also retrieve command executions without using the
Command History dialog box. The Previous Command and Next Command menu
items and input toolbar icons retrieve commands from the command history
and enter them in the input pane, completely replacing the existing contents
of the input pane. For more information on the Previous Command and Next
Command menu items, see Worksheet Menu
on page 13-6.
The Spool menu item, Save Output As icon, and Save Worksheet
As icon allow you to save the work you have performed in a SQL Worksheet.
Each of these Worksheet menu commands displays the standard file selection
dialog box, which you can use to specify the file in which to save your
work.
The Spool menu item allows you to dynamically save all output
of your worksheet session to a specified file. SQL Worksheet continues
to save the output of your worksheet until you choose Stop Spooling from
the menu or when you exit the SQL Worksheet.
The Save Output As allows you to write the current contents
of your worksheet's output pane to a specified file. The output pane normally
contains the last 32 KB of output and the last 100 lines of text. The actual
amount may vary according to the amount of available system memory. Any
text that has scrolled out of the output pane is also written to the file.
The Save Worksheet As menu item and icon (located in the
input pane toolbar) writes the contents of the worksheet's input pane to
a specified file.
The SQL Worksheet appends a file extension, such as .log
or .txt, to the name of each file created by the Spool, Save Output As,
and Save Worksheet As commands.
From a SQL Worksheet, you can run any script that contains SQL, PL/SQL, or Server Manager DBA commands. There are four ways to run scripts from a SQL Worksheet:
Clicking the Open Worksheet icon in the input pane toolbar
or selecting the Open menu item displays the standard file selection dialog
box. After you select the file and click OK, the content of the SQL script
appears in the worksheet's input pane. Click the Execute icon or select
the Execute menu item to run the script.
Selecting the Run Script menu item also displays the Run
Script dialog box. Select the desired script and click OK. The Server Manager
DBA @ command appears with the full path and filename of the selected script
in the input pane. The script is run automatically.
To drag and drop scripts from the Windows file manager, select
the desired script in the file manager and drop it into the input pane.
The content of the script appears in the input pane. Click the Execute
icon to run the script.
You can also run a script from the input pane of a worksheet
by entering an @ command followed by the script name. The following figure
illustrates running a script from the input pane of a worksheet.
In a script you can run other scripts. In the original script
you can include the line @@second_scriptname to run another script
called second_scriptname. The @@ command indicates that the second
script is in the same directory as the original script.