• 7
• SQL*DBA
• Introduction to Oracle SQL*DBA
• The SQL*DBA Command Set
• Before You Start SQL*DBA
• Connecting to a Database
• Terminating SQL*DBA Commands
• SQL*DBA Operation Modes
• SQL*DBA in Line Mode
• Starting SQL*DBA in Line Mode
• Startup and Shutdown with SQL*DBA in Line Mode
• SQL*DBA in Command Mode
• Automating the Startup and Shutdown Options
• SQL*DBA in Menu Mode
• Parts of the Menu
• Navigating in SQL*DBA
• SQL*DBA Menus
• SQL*DBA Command Reference
• ARCHIVE LOG
• CONNECT
• DISCONNECT
• EXECUTE
• EXIT
• HOST
• MONITOR
• PRINT
• RECOVER
• SET
• SHOW
• SHUTDOWN
• SPOOL
• STARTUP
• Summary

7

SQL*DBA

The purpose of this chapter is to familiarize you with the basic, mid-level, and many advanced functions of SQL*DBA. It should also help you to put that knowledge to use in your current and future environments. Although this chapter's coverage is not exhaustive, it should certainly give you all the requirements you need to forge ahead in understanding the more advanced concepts of database management with SQL*DBA.

The following screen captures and sample programs come from a DG/UX Aviion 9500, 8-processor UNIX system running a DG/UX 5.4.3.10 MU02 operating system with Oracle RDBMS Version 7.1.4. The output or code could run differently on your system.

Introduction to Oracle SQL*DBA

To assist in the setup, administration, and day-to-day operations of your Oracle databases, Oracle Corporation provided a tool called SQL*DBA. Although SQL*DBA has many functions, its most obvious use is to start up and shut down local databases. If you have SQL*Net installed, SQL*DBA uses its features to start up and shut down remote databases as well. This versatility gives the database administrator great flexibility in database management.

Secondary functions of SQL*DBA include altering database and system statistics, modifying the characteristics of a database, administering users and security, restoring a database, and manipulating data files belonging to the databases. With the added capability to monitor various aspects of the database as it is running, SQL*DBA is an important and multifaceted tool.

SQL*DBA has three modes of operation:

Line mode

Command mode

Menu mode

Line mode is a non-graphical interface that enables the user to interactively enter commands. The output is scrolled across the user's screen. This mode is very useful for managing portions of the database that do not require the ease of screen mode.

Command mode is identical to line mode with the exception of how it is used. Although line mode is interactive by nature, command mode is intended to run in batch mode. Generally, command mode is used to run a script, or collection of commands, created by the user.

Menu mode, also referred to as screen mode, is a graphical interface that you can use on supported ASCII terminals, including X terminals. It provides the user with a menu-driven interface from which they can issue most SQL*DBA commands.

Please be aware that some versions of SQL*DBA (such as those supplied with Oracle Version 6 and Personal Oracle) might not support the menu (screen) mode, as well as shortcut keys.

Although the menu interface is quite powerful, a handful of commands can only be executed from the command line. Table 7.1 contains a list of those commands. Screen mode supports all the commands that line mode supports in addition to a feature called monitor, which I discuss later in this chapter.

Table 7.1. SQL*DBA commands available only through the command line.

Command

Purpose

Each of the modes is covered in greater detail in later sections. First, it is important to cover a few topics before proceeding to the commands.

The SQL*DBA Command Set

SQL*DBA accepts all standard SQL, PL/SQL, and SQL*DBA commands with few exceptions. Exceptions to this rule include SQL*Plus formatting commands such as set heading or set linesize. You can find a complete list of SQL*DBA specific commands in the command reference at the end of this chapter.

Before You Start SQL*DBA

There are several requirements to meet before you can run SQL*DBA. First, the user must either own the executable or be a member of the group associated with it. You can find the SQL*DBA executable in the bin directory of your ORACLE_HOME environment variable; it's usually named sqldba (DOS-based systems might include an EXE or COM extension). If you are running Oracle on a DOS machine, the executable is located in the directory pointed to by your XBIN environment variable.

Once you can execute SQL*DBA, you must also have privileges to execute the specific commands you want to use. For instance, if you want to add a data file to a tablespace, you must have the ALTER TABLESPACE privilege.

Next, it is important to know how to connect to a database. Nearly all the commands supported by SQL*DBA require a connection to a database (especially startup and shutdown options).

Connecting to a Database

All SQL*DBA modes support the connect commands. With a username/password specification, you can connect to your default database. SQL*Net supports special connect strings to connect to remote databases on your network. You can use this option to eliminate the need for logging into every system where a database is running.

connect internal is the most typical connection using SQL*DBA. Internal, a special username viable only through SQL*DBA, is actually a alias for logging into the SYS account. Intended strictly for use with special operations such as startup and shutdown, the internal username is limited to users with the correct access to SQL*DBA (ownership and group access to the executable).

Terminating SQL*DBA Commands

SQL*DBA, like SQL*Plus, enables you to execute multiple-line commands. By default, you must terminate all commands with a semicolon (;) or forward slash (/), regardless of which mode you are using. These characters tell SQL*DBA to execute the command. Some commands might not require the terminator. If you press return while entering a command where SQL*DBA expects further input, SQL*DBA provides a continuation line where you can enter the next part of the command line. Entering the termination character ends the command and begins execution.

SQL*DBA Operation Modes

I mentioned earlier the three modes of operation for SQL*DBA; only one major difference distinguishes the three modes. Screen mode is the only mode capable of running monitor programs that help you monitor various aspects of your instance. Apart from this difference, all three modes function identically. Commands that you can issue in line mode work identically when issued in either screen or command modes.

SQL*DBA in Line Mode

Line mode places the user into a line-driven, interactive interface. This mode does not support menus or additional input devices other than the keyboard. You usually use line mode for quick access to SQL*DBA commands or for automating different aspects of its functionality.

Because line and screen mode are identical in nature except for monitor commands, I limit the scope of this discussion on line mode to starting SQL*DBA in line mode, starting up and shutting down the database, and automating the startup and shutdown processes. The following section on screen mode goes into much greater depth about the commands available in SQL*DBA.

Line mode is provided strictly for backward compatibility with older versions of Oracle. Oracle announced that it will not support SQL*DBA in future releases. At press time, SQL*DBA is still a supported product.

Starting SQL*DBA in Line Mode

To start SQL*DBA in line mode, you can enter one of the following commands: sqldba mode=line or sqldba lmode=y. After starting SQL*DBA, you should see a prompt that resembles SQLDBA>.

At this point, you can connect to the database using connect internal or connect username/password. Once you are finished with your SQL*DBA session, you can issue the exit command to leave.

SQL*DBA is not case sensitive unless you are dealing with the selection of data from the database. Uppercase or lowercase letters are perfectly legitimate.

Startup and Shutdown with SQL*DBA in Line Mode

As stated before, the primary use for SQL*DBA is the startup and shutdown of your database. You can do this from line or screen mode, but it seems the most common way is from line mode.

You cannot issue startup and shutdown commands from a connection via the Multi-Threaded Server. You must have a dedicated session to issue these commands. If running the Multi-Threaded Server, you must first disable the TWO_TASK environment variable before starting SQL*DBA.

To start up a database, use the following set of commands.

1. Start SQL*DBA by issuing the sqldba command.
   
   
2. Issue the CONNECT INTERNAL command to connect to the database.
   
   
3. Start the database using any of the STARTUP commands.
   
   
4. Exit SQL*DBA by typing EXIT.
   
   

You can substitute any one of the startup options for the STARTUP command listed in step 3, such as STARTUP MOUNT, or STARTUP FORCE. Later on, I discuss the startup and shutdown options in greater depth.

To shut down the database from line mode, use the following steps:

1. Start SQL*DBA by issuing the sqldba command.
   
   
2. Issue the CONNECT INTERNAL command to connect to the database.
   
   
3. Shut down the database using any of the SHUTDOWN commands.
   
   
4. Exit SQL*DBA by typing EXIT.
   
   

You can substitute other shutdown options for the SHUTDOWN option listed in step 3, depending on your site's needs. An example is using the SHUTDOWN IMMEDIATE command if you want to log out all processes connected to the database.

SQL*DBA in Command Mode

Command mode enables you to place a group of commands in the same file to be executed together. Various uses of command mode include starting up or shutting down an instance and collecting data for a custom report.

The format and execution of a command file for use in SQL*DBA is identical to the format of command files used in SQL*Plus. The following is a list of guidelines you can use for formatting a command file.

Although the SQL extension is not required, using it could help you identify the file in the future.

If any commands in your command file require you to be connected to the database, the first line should be CONNECT INTERNAL.

You should terminate all single-line or multiple-line commands with ; or /. If the command is longer than a single line, only the last line of the command must have a ; or / terminating it.

You can separate command lines with a return for readability.

Scripts can call other scripts, but the depth of the nesting is operating-system dependent.

The command mode does not require an EXIT command.

To call a command script while executing SQL*DBA, you can use the following command format:

sqldba command="@filename.sql"

SQL*DBA requires the quotes and the @ to execute the script properly. The following is a sample startup script:

REM

REM startup.sql to be used to automate the startup

REM of a database through SQL*DBA command mode

REM

connect internal;

startup;

With the preceding script, you can start up your database with the following command:

sqldba command="@startup.sql"

The following is a sample shutdown script:

REM

REM shutdown.sql to be used to automate the shutdown

REM of a database through SQL*DBA command mode

connect internal;

shutdown;

You can then execute this script in same way you executed startup.sql:

sqldba command="@shutdown.sql"

Make sure that startup.sql and shutdown.sql reside in your current directory or somewhere within your path. If SQL*DBA cannot find your script, you are given an error and returned to a SQLDBA> prompt instead of your operating system prompt. It is a good idea to explicitly name the directory and file that you want to execute.

You can further customize these scripts by adding startup and shutdown options (such as shutdown immediate) or SQL statements that show who is currently logged into the Oracle instance.

Automating the Startup and Shutdown Options

With a certain amount of operating system expertise, you can automate the startup and shutdown of your Oracle instance. This can be helpful if you have an operations staff that routinely must start and stop your database instances for basic maintenance (system reboot, offline backup, and so on).

The previous section discussed one possible way to automate startup or shutdown using command scripts. Another, more typical way is to embed these commands in a script that a system operator could run without any knowledge of Oracle commands or SQL*DBA modes. The following is a sample UNIX script for starting an Oracle instance.

#! /bin/sh

##########

# filename:  oracle_start.sh

#####

# As Oracle recommends, the first line is to force the script

# to run in the Bourne Shell.

#####

#####

# This script should be run from the Oracle DBA account.

# It assumes that ORACLE_HOME has been defined.  If it has not,

# this script will exit with the appropriate error message.

#

# Other assumptions include that your ORACLE_SID has been set before

# running this script.

#

#####

# If ORACLE_HOME = nothing then exit with a status of 1

if [ "${ORACLE_HOME}" = "" ]

  then

    echo "ORACLE_HOME is undefined.  It must be defined before"

    echo "continuing."

    exit 1

fi

# If ORACLE_SID is undefined, exit with a status of 2

if [ "${ORACLE_SID}" = "" ]

  then

    echo "ORACLE_SID is undefined.  It must be defined before"

    echo "continuing."

    exit 2

fi

# Check to see if the database is up

# if the sgadef(instance).dbf file is there, the instance is

# running and the startup should NOT proceed

if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ]

  then

    echo "The ${ORACLE_SID} instance of Oracle is running"

    echo "You must shutdown before starting up."

    exit 3

fi

# The database is not running, so let's start it in normal mode

# using the script we defined in the preceding sections.

# If we cannot find sqldba, then we will exit with an error

if [ -f "${ORACLE_HOME}/bin/sqldba" ]

  then

    sqldba command="@startup.sql"

  else

    echo "Could not locate the sqldba executable.  Startup cannot"

    echo "proceed."

    exit 4

fi

# Now let's check to see if it came up.

if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ]

  then

    echo "The instance has now started."

  else

    echo "The instance is NOT started.  Please check for errors "

    echo "before attempting to restart the database."

    exit 5

fi

exit 0

##################################

The following code is a sample shutdown script. This program is almost identical to the startup script except that is uses shutdown.sql and reverses the logic in checking for the sgadef(instance).dbf file.

#! /bin/sh

##########

# filename:  oracle_stop.sh

#####

# As Oracle recommends, the first line is to force the script

# to run in the Bourne Shell.

#####

#####

# This script should be run from the Oracle DBA account.

# It assumes that ORACLE_HOME has been defined.  If it has not,

# this script will exit with the appropriate error message.

#

# Other assumptions include that your ORACLE_SID has been set

# before running this script.

#

#####

# If ORACLE_HOME = nothing then exit with a status of 1

if [ "${ORACLE_HOME}" = "" ]

  then

    echo "ORACLE_HOME is undefined.  It must be defined before"

    echo "continuing."

    exit 1

fi

# If ORACLE_SID is undefined, exit with a status of 2

if [ "${ORACLE_SID}" = "" ]

  then

    echo "ORACLE_SID is undefined.  It must be defined before "

    echo "continuing."

    exit 2

fi

# Check to see if the database is down

# if the sgadef(instance).dbf file is not there, the instance is

# not running and the shutdown should exit

if [ ! -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ]

  then

    echo "The ${ORACLE_SID} instance of Oracle is not currently

    echo "running.  You must startup before you can shutdown."

    exit 3

fi

# The database is running, so let's do a normal shutdown

# using the script we defined in the preceding sections.

# If we cannot find sqldba, then we will exit with an error

if [ -f "${ORACLE_HOME}/bin/sqldba" ]

  then

    sqldba command="@shutdown.sql"

  else

    echo "Cannot locate sqldba.  Cannot continue the shutdown."

    exit 4

fi

# Now let's check to see if it was shut down

if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ]

  then

    echo "The instance is still running, or an error"

    echo "occurred while trying to shutdown the instance."

    echo "Please refer to the alert log for any errors that"

    echo "might have occurred."

    exit 5

  else

    echo "The instance was shut down."

fi

exit 0

##########

SQL*DBA in Menu Mode

Although there are many arguments about whether a menu interface is faster or more efficient as opposed to an interactive line interface, there is no doubt that a menu interface is much easier to use. Otherwise, you'd have to face memorizing the myriad of commands that are available to SQL*DBA. First, I discuss the various components of the menu, and then, I discuss exactly what the menus do.

Parts of the Menu

There are four important parts of the display that are pointed out in Figure 7.1.

Figure 7.1. SQL*DBA menu items.

On the menu bar across the top of your screen should appear ten choices: File, Edit, Session, Instance, Storage, Log, Backup, Security, Monitor, and Help. Each of these choices represents a menu or set of menus to assist in executing various SQL*DBA commands.

Notice that each option has a single underlined letter. These letters, or shortcut keys, are quick picks to the menu option. After navigating to the menu bar, you can type the underlined letter to display that menu selection. When you are familiar with the menus, this option makes menu selection much quicker.

Just below the menu bar is the output window. This window displays the output to each command you type and scrolls down as it fills.

The bottom of the screen is devoted to the input window, which is where you enter all input. The input window must be the current window to accept or display your keystrokes.

To the right of the output and input windows are two scroll bars (one for each window). These bars tell you where you are in the current windows and how much more information is left to display.

You can tell which window is the active one by searching for your cursor. As you set your focus to different windows, your cursor follows.

Each menu could request more information, ask you to select specific options, or provide you with a list of items to choose from. In some versions of SQL*DBA and for some selections, you'll notice a keystroke command on the right of the menu option (Esc,P, for example). You can use that shortcut key as another way to reach the menu. The following lists the various types of objects associated with the different menus.

Drop-down menus—Each menu item shown with a > to the right of the selection has a drop-down menu. The options help further define and limit the operation being performed.

Alerts—At various times in your SQL*DBA session, an alert window might appear to caution you about the operation you are about to perform. These windows give you the chance to confirm or cancel the operation. Select the action that is appropriate.

Dialog boxes/fields—These objects contain fields where you must enter specific data, such as role names and filenames. When the requested information is required, the word MANDATORY is displayed in the bottom-left corner. If there is a list associated with the menu item, the word LIST is displayed in the bottom-left corner as well.

Data entry fields97ÄLocated inside dialog boxes, data entry fields enable you to enter values or select from a list of values.

Radio buttons—A radio button is an option field indicated with ( ). You can select only one item in a list of radio buttons at a time. Use the spacebar to select or deselect the options.

Checkboxes—A checkbox is an option field indicated with [ ]. You can check as many of these boxes as apply. As with radio buttons, you use the spacebar to select or deselect the option(s).

Navigating in SQL*DBA

If you don't know the navigational keystrokes, using SQL*DBA can be a difficult to nearly impossible task. All keys are defined through the Oracle*Terminal package and can be redefined by the user. The examples listed throughout this section use the default keystrokes for a VT-220 terminal.

Knowing how to get a complete listing of all keystrokes will put you far ahead in the game. Once in SQL*DBA, press Esc,K or Ctrl+K. This places you in the Key Help for Text Editor window. There are two columns of data: Function and Keys. Function describes what the Keys (or keystroke) option does, and the Keys option shows the keystroke used to issue the command.

Table 7.2 contains a partial listing of the more critical commands required for navigating the menus. You can find a complete listing of Oracle's predefined functions by using the show keys command or by running Oracle*Terminal and examining the keys defined for your terminal type.

Table 7.2. Important SQL*DBA navigational keys for VT-220 terminals.

Function   Keys   Description

The PF4 and K0 keys are VT-100 style keys. Generally, K0 is the 0 key on your numeric keypad. PF4 varies depending on the keyboard style, keyboard mapping, and terminal program, if applicable.

You use a typical navigation sequence when you start up your database. One way to accomplish this task is to issue the Menu command, use your arrow keys to move over to Instance, press Return, move your cursor over the Start Up command, press Return, and then follow the different prompts (navigating with the spacebar and tab keys), pressing Return when you reach OK.

Another quicker way is to issue the Menu command, press I (the underlined letter in Instance), press U (the underlined character in Start Up), and then navigate through the prompts until you get to OK.

Finally, you could issue the shortcut command Esc,P, navigate through the menu to choose your startup options, and select OK when finished.

As you can see, you can use many different options to navigate through the menus. The ways you choose to navigate depend on your familiarity with them and what kind of terminal you have. There is no right or wrong way, only preferred ways.

If you have a terminal that supports mouse input, one final way to use the menus is to move your cursor to the menu selection and click the mouse button.

SQL*DBA Menus

The menus in SQL*DBA provide almost the entire range of the command set. You must enter many different menus and options before a command is accepted and executed. Being familiar with these menus can greatly assist you in identifying and solving problems of all kinds. The following section explains the menus of SQL*DBA and how to use them. I also provide the actual line mode commands along with some tips on their uses.

Throughout all the menus, you will notice lines separating various menu options into related groups. For example, in the Security menu, you will notice lines between the User, Role, Profile, and Grant commands. These groupings are very helpful in identifying unknown menu options.

SQL*DBA File Menu

The File option, shown in Figure 7.2, has four separate options: SQL Script, Spool On, Spool Off, and Quit.

Figure 7.2. SQL*DBA File menu.

You use SQL Script to execute a SQL script file while still in SQL*DBA. After selecting this option, you are provided with a dialog box containing two options. The first is whether to Use Default File Path Name and File Extension. If you select this option, SQL*DBA searches through your path environment variable for the file. The second option is the SQL Script to Execute (Mandatory). You choose the name of the file to run. Specify the command on the line provided and select OK.

It is not necessary to place a SQL extension on the filename you specify. SQL*DBA automatically does this for you. If the file does not end with a SQL, however, you have to specify the correct extension. SQL*Plus script files do not have to end with a SQL.

After you select OK, the dialog box disappears, and the output scrolls down in the output window of your screen. Use the Next Group key to navigate to the output window to scroll through the output generated by your script.

Spool On requests a filename (mandatory) to send output to. This file is a log of all input and output generated during your SQL*DBA session with the exception of monitor output. Because the monitor output is changed every cycle, it cannot be saved to a spool file. You are limited to having only one spool file open at any time. If you attempt to spool to a second file, the first file closes before a second one opens.

Command type: SQL*Plus

Line mode command: SPOOL filename

Required privileges: Write permissions for the current directory.

Spool Off closes the previously opened spool file (if any). SQL*DBA automatically keeps track of filenames and closes the currently open file. If you have no previously opened spool file, SQL*DBA generates an appropriate error message.

Command type: SQL*Plus

Line mode command: SPOOL OFF

Required privileges: None

Use Spool On and Spool Off to log output of an interactive session and help create a command file that you can run from line mode, such as the commands generated by the SQL*DBA menus. This option is very helpful in setting up automated custom reports.

Quit, which had a shortcut of Esc,Q, brings up a caution window asking whether it is really OK to quit your session or if you want to cancel the quit session command. Selecting OK exits you to your operating system prompt.

Command type: SQL*Plus, SQL*DBA

Line mode command: EXIT

QUIT

Required privileges: None

SQL*DBA Edit Menu

The Edit menu in Figure 7.3 provides you with Cut and Paste commands, as well as Previous Command and Next Command options. Please note that there are no associated SQL*DBA commands for these options.

Figure 7.3. Edit menu.

Choosing the Cut command cuts the currently selected text. Copy copies the previously selected text from the buffer to your current location in the menu. Paste copies the currently selected text into the edit buffer. Clear erases the currently selected text. It has no effect on the clipboard buffer.

Previous Command and Next Command both scroll through the last ten commands executed from the input window. Choosing these commands can be quicker than trying to retype one of your previous commands. Once you have selected the command you want to reissue, press Return to execute it.

The number of saved history commands is ten. You can modify this with the SET HISTORY command while in SQL*DBA. This setting is only effective for the current SQL*DBA session.

SQL*DBA Session Menu

Figure 7.4 shows the Session menu. The Connect command requests a username (mandatory) and a password in order to connect to the database. Specifying internal at the username data entry field connects you as a privileged user.

Figure 7.4. Session menu.

Command type: SQL*DBA

Line mode command: CONNECT

Required privileges: CREATE SESSION

Disconnect does the opposite of connect; that is, it disconnects you from the database. In this mode, you can issue only commands not requiring access to database tables.

Command type: SQL*DBA

Line mode command: DISCONNECT

Required privileges: None

Enter System Command (mandatory) enables you to execute operating system commands (or programs) from within SQL*DBA. Once this field is entered and accepted, you see an operating system window where the output from your command or program is displayed. Once the command finishes, you get a prompt to Hit Any Key to Continue. After pressing any key, you return to the SQL*DBA window.

Command type: SQL*DBA

Line mode command: HOST

Required privileges: None

When executing a command with the HOST option, SQL*DBA does not use any form of paging so that your output is displayed page by page. If you want this sort of option, you must add those commands, such as MORE or PG, to your host command.

Go to System takes you to an operating system window where you can enter multiple commands (as if you had exited SQL*DBA completely). It does not, however, make a complete copy of your environment, so some aliases or environment variables might not work or display correctly. You can figure this out by trial and error.

Command type: SQL*DBA

Line mode command: HOST

Required privileges: None

Set Server Output On enables debugging output from stored procedures that use the DBMS_OUTPUT PUT() and PUT_LINE() commands. The size option enables you to specify, in bytes, the length of the message buffer that can be accumulated at one time. If the buffer fills before calls to get message can make more room for additional data, an error is returned to the program or procedure that is sending the message. The minimum for this buffer is 2,000 bytes.

Command type: SQL*DBA

Line mode command: SET SERVEROUT

Required privileges: None

Set Server Output Off disables those messages. These two command are very useful in coding debug information into your procedures.

Command type: SQL*DBA

Line mode command: SET SERVEROUT

Required privileges: None

SQL*DBA Instance

The Instance menu options are shown in Figure 7.5. The menu enables you to start, stop, and configure various characteristics of how the instance will run. It has the following options: Start Up, Shut Down, Mount Database, Open Database, Force Checkpoint, Force Log Switch, Configure Dispatcher, Configure Shared Server, Prevent Connection, Allow Connection, and Kill Session.

Figure 7.5. Instance menu.

You use the Start Up option to mount and open the database for maintenance or use. You specify in the dialog box exactly how to bring this instance online.

Command type: SQL*DBA

Line mode command: STARTUP

Required privileges: Capability to connect as INTERNAL

Shut Down enables you to specify one of three ways to bring your database down: Normal, Immediate, or Abort. These options are explained further in the command reference.

Command type: SQL*DBA

Line mode command: SHUTDOWN

Required privileges: Capability to connect as INTERNAL

As mentioned throughout this chapter, you should use SHUTDOWN ABORT with extreme caution because it can cause data corruption!

Mount Database enables you to mount the database in either Exclusive mode (only a single instance can access the data files) or Parallel mode (multiple instances have access to the data). You must be running the Oracle Parallel Server option to make full use of the Parallel option.

Command type: SQL*Plus

Line mode command: ALTER DATABASE MOUNT

Required privileges: Capability to connect as INTERNAL

The Open Database option enables you to bring the database from a mounted state to the open state. Even though the database might be mounted, the data is not accessible until it is in the open state.

Command type: SQL*Plus

Line mode command: ALTER DATABASE OPEN

Required privileges: Capability to connect as INTERNAL

You use Force Checkpoint to checkpoint either the local instance or all of the instances in your environment. If you are not running the Parallel Server option, both options work the same.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM CHECKPOINT

Required privileges: ALTER SYSTEM

The Force Log Switch option forces the current instance to switch to the next available log file in the thread. Once the database has made the switch, the previous log group becomes available for archiving or maintenance.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM SWITCH LOGFILE

Required privileges: ALTER SYSTEM

The next two commands are for Oracle's Multi-Threaded Server configuration (MTS).

Configure Dispatcher enables you to configure the number and type of running dispatchers. You can either add or remove dispatchers of various protocols to your system. This command effectively modifies the mts_servers parameter for the duration of the instance. It does not reset the MTS parameters in the INIT.ORA file, nor can it go beyond the value of mts_max_servers specified at startup (in the INIT.ORA file).

Command type: SQL*Plus

Line mode command: ALTER SYSTEM SET MTS_DISPATCHERS

Required privileges: ALTER SYSTEM

To configure the number of shared servers currently running, you can use the Configure Shared Server option. The dialog box that appears asks you how many shared server processes to run at one time, which must be in the range of mts_servers to mts_max_servers (found in your INIT.ORA file). You cannot go below or above these values.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM SET MTS_SERVERS

Required privileges: ALTER SYSTEM

Prevent Connection enables you to limit connections to the database to only those users that have been granted the RESTRICTED SESSION privilege. Users without this privilege receive an error message stating that only specific users are permitted access.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM ENABLE RESTRICTED SESSION

Required privileges: ALTER SYSTEM

Allow Connection enables all authorized users to gain access to the system again. These two commands are extremely helpful when you're doing database maintenance that requires the database to be online but unused.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM DISABLE RESTRICTED SESSION

Required privileges: ALTER SYSTEM

When you choose the Kill Session option, a dialog box appears listing all the current users, their session number, and their serial number. The combination of these two numbers provides a unique identification based upon a single user session, which you can use to monitor or kill the session.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM KILL SESSION

Required privileges: ALTER SYSTEM

SQL*DBA Storage Menu

The Storage menu enables you to manipulate all aspects of a tablespace or rollback segment, including creating and dropping them and choosing default storage options. This could be one of the most useful menus for database management.

Figure 7.6 displays the Tablespace drop-down menu under the Storage menu. The Tablespace option enables you to Create, Drop, Set Online, Set Offline, Add a Data File, Rename a Data File, and Modify Default Storage of database tablespaces.

Figure 7.6. Tablespace drop-down menu.

Tablespace Create creates a tablespace with the name you specify.

Command type: SQL*Plus

Line mode command: CREATE TABLESPACE

Required privileges: CREATE TABLESPACE

Tablespace Drop drops the tablespace. If the tablespace is not empty (it contains tables or objects), you have to use the INCLUDING CONTENTS option.

Command type: SQL*Plus

Line mode command: DROP TABLESPACE

Required privileges: DROP TABLESPACE

Set Online sets a tablespace online. This is the default, but if the instance starts up with errors or a tablespace requires recovery, the tablespace is offline.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace ONLINE

Required privileges: ALTER TABLESPACE

Set Offline takes a tablespace offline. You use this primarily for database maintenance. There are three options in taking a tablespace offline:

After Checkpointing—This option checks to ensure all data files belonging to the tablespace are writeable, runs the checkpointing process, and brings the tablespace offline once checkpoint is done. This corresponds to Normal mode.

After Checkpointing Online Data Files—This option checks only the data files belonging to the tablespace that are online. Once that is finished, it begins the checkpoint process and brings the tablespace offline. This option does not wait for the checkpoint to finish, so media recovery might be necessary before you bring the tablespace back online. This corresponds to Immediate mode.

Without Checkpointing—This option brings the tablespace offline immediately without waiting for a checkpoint. This option is helpful in beginning immediate maintenance on a tablespace. Media recovery is necessary to bring this tablespace online again. This option is only available if you are running your instance in ARCHIVELOG mode. This corresponds to Abort mode.

Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace OFFLINE
Required privileges: ALTER TABLESPACE

Add Data File is useful for adding additional data to a tablespace that has grown beyond its first data file. Although it is better to have a single data file at the size you need, multiple data files are a short-term solution.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace ADD DATAFILE

Required privileges: ALTER TABLESPACE

You use Rename Data File for relocating data files from one disk to another. This is extremely useful in tuning your database. For example, when you have several tablespaces that are active and reside on the same disk, you should move them apart to alleviate disk contention. It is important to note that this command does not rename or move the file. It only replaces the old name with the new name you specify. You must first take the tablespace offline and then use your operating system commands to relocate and rename the data file. Once you have done that, you can issue the database commands to rename the data file.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace RENAME FILE

Required privileges: ALTER TABLESPACE and operating system privileges

Alter Default Storage modifies the storage options for the tablespace. These options include Percent Free, Percent Used, Initial Extents, and so on. If you create a table within a tablespace but do not give it any storage options, the new table gets the default storage options given to the tablespace.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace STORAGE

Required privileges: ALTER TABLESPACE

The Rollback Segment drop-down menu, shown in Figure 7.7, enables you to manage the creation, deletion, and basic utilization of your rollback segments. The drop-down menu has the following options: Create, Drop, Set Online, Set Offline, and Alter Storage.

Figure 7.7. Rollback drop-down menu.

Create enables you to specify a rollback segment name, tablespace name, and storage options. You can create rollback segments as Public and Private. Public rollback segments are available to all instances sharing the database (Parallel Server option), and a Private rollback segment is used strictly for one instance.

Command type: SQL*Plus

Line mode command: CREATE ROLLBACK SEGMENT

Required privileges: CREATE ROLLBACK SEGMENT

The Drop command deletes an offline rollback segment. If the rollback segment is still online, the operation fails with the following error message:

ORA-1545: rollback segment 'rbs01' specified not available

Command type: SQL*Plus

Line mode command: DROP ROLLBACK SEGMENT

Required privileges: DROP ROLLBACK SEGMENT

Set Online takes an offline rollback segment and makes it available for use in the database. This operation takes effect immediately if the tablespace that the rollback segment resides in is currently usable.

Command type: SQL*Plus

Line mode command: ALTER ROLLBACK SEGMENT segment_name ONLINE

Required privileges: ALTER ROLLBACK SEGMENT

Set Offline enables the DBA to take a rollback segment offline. This is very handy when you must drop and recreate a rollback segment that has reached maximum extents or when you relocate a rollback segment. If there are active transactions in the rollback segment, the database prevents new transactions from accessing the segment and waits until the current transactions are committed or rolled back before taking it offline.

Command type: SQL*Plus

Line mode command: ALTER ROLLBACK SEGMENT segment_name OFFLINE

Required privileges: ALTER ROLLBACK SEGMENT

Alter Storage alters the default storage options of the rollback segment. You use this for next extents because you cannot change the initial extent once the rollback segment is created. In order to change the initial extent, you must drop the rollback segment and recreate it with the correct value.

Command type: SQL*Plus

Line mode command: ALTER ROLLBACK SEGMENT segment_name STORAGE

Required privileges: ALTER ROLLBACK SEGMENT

SQL*DBA Log Menu

Figure 7.8 shows the Log menu. These log options control all aspects of the redo log files. Log files are a critical part of the online recovery method and are extremely important if you are running in ARCHIVELOG mode. In all databases, they can be a source of disk contention if your database is very active. If a redo log file is corrupted or lost and you are running in ARCHIVELOG mode, you cannot completely restore your database!

Figure 7.8. Log menu.

Split your redo log files across several disks to eliminate high amounts of disk I/O. In ARCHIVELOG mode, once a redo log fills and the log switches to another redo log file, the filled log is then written out to an archive log file. If your next redo log is on the same disk, there is added I/O while the filled log is written to disk and the new log is being written to. Alternating log files between two or more disks prevents this bottleneck from occurring.

A group is a set of mirrored redo log files that consists of one or more actual files. If you are using some form of disk mirroring or RAID technology, it is unnecessary to have more than one file per group because your system automatically employs a recovery method if you lose a disk where the redo logs reside.

The Add and Drop Group options on the Log menu enable you to add or remove groups from your currently running database. This is extremely helpful if you find that you are filling log files faster than they are written to disk.

The Add Group command enables you to add a group to your currently running database.

Command type: SQL*Plus

Line mode command: ALTER DATABASE ADD LOGFILE GROUP

Required privileges: ALTER DATABASE

The Drop Group command enables you to drop an entire log file group from the database. This does not remove the files, however. The files can be reused (specifying REUSE instead of SIZE 50K in a creation statement) or deleted through your operating system commands if the files should be deleted. The Drop Group option is useful in helping to eliminate disk contention as well as increasing the number of redo log files available for a database.

Command type: SQL*Plus

Line mode command: ALTER DATABASE DROP LOGFILE GROUP

Required privileges: ALTER DATABASE

Each redo log file of a group is considered a member, and for consistency within each group, there should be an equal number of members. This is not a requirement but a suggestion. If you are running a mirrored or RAID system, it is unnecessary to have more than one member per group because the system is handling the mirroring. Adding or removing members is helpful in defining a high availability system.

Creating multiple group members provides a form of failsafe should you have a disk failure. If you lose all the files in a redo log group, the database halts when trying to write to that group. If you place group members on separate disks, Oracle marks that file as offline but continues to function if one disk fails.

By the nature of mirrored or RAID systems, should a single disk fail, the other members of the mirror or RAID group should still function. Because adding multiple members provides this same type of failsafe, you can eliminate the overhead of maintaining more than one redo log file without sacrificing the failsafe of a single point of failure.

To use the Add Member option, you need to decide on the name of the file and which group you want to add it to.

Command type: SQL*Plus

Line mode command: ALTER DATABASE ADD LOGFILE MEMBER

Required privileges: ALTER DATABASE

The RENAME MEMBER option is really just the RENAME DATA FILE command in disguise. As with the RENAME DATA FILE command, RENAME MEMBER only associates the name of the redo log member with a new file name inside the database. The member must be offline, and before you issue the RENAME MEMBER command, you must use your operating system commands to relocate or rename the data files in question.

Command type: SQL*Plus

Line mode command: ALTER DATABASE RENAME FILE

Required privileges: ALTER DATABASE

Using the Drop Member option is helpful if you are reconfiguring your system to use mirrored disks and want to reclaim disk space or if you are resizing your group files. The group the member is in must be offline before you can drop it.

Command type: SQL*Plus

Line mode command: ALTER DATABASE DROP LOGFILE MEMBER

Required privileges: ALTER DATABASE

A thread is a collection of two or more redo log groups. Together, these groups form a sequence of log files that the database rotates through. When one log file or group is filled, the database switches to the next group. When it reaches the last group in the thread, it cycles back to the first.

A single database that is not running in Parallel Server mode has only one thread. If a database is running in parallel, there should be one thread for each instance accessing the database. Public and Private denote the type of access for a given thread. Private means the thread is reserved for a specific instance, and Public means the thread is available for any instance to reserve at startup, if the instance does not specifically request a certain thread.

Using the Enable Thread option makes a thread available for the next instance that requests it.

Command type: SQL*Plus

Line mode command: ALTER DATABASE ENABLE THREAD

Required privileges: ALTER DATABASE

You use the Disable Thread option to take a thread offline for maintenance or to make it available for another instance. If an instance has the thread in question mounted, the command fails.

Command type: SQL*Plus

Line mode command: ALTER DATABASE DISABLE THREAD

Required privileges: ALTER DATABASE

The Start, Stop, Begin, and List Archive options refer directly to archiving redo logs to disk. Automatic archiving enables the database to write the contents of a filled (or manually switched) log file to disk and free up the redo log for use.

Start Auto Archive tells the system to automatically write data from the redo logs to disk when it's finished with the redo log. You must specify a location and the beginning part of the filename. You can do this either in the INIT file for the database or on the command line when you start Auto Archive mode.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM ARCHIVE LOG START

Required privileges: ALTER SYSTEM

Stop Auto Archive disables the system from writing filled log files to disk. If you are extremely low on disk space or you're monitoring certain aspects of the database, this option can be helpful while you relocate files.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM ARCHIVE LOG STOP

Required privileges: ALTER SYSTEM

Use Begin Manual Archive if you are not running in automatic archive log mode and you want to start writing redo logs to disk. The system can write redo logs according to age, change number, group and thread numbers, or all of these designations.

Command type: SQL*Plus

Line mode command: ALTER SYSTEM ARCHIVE LOG

Required privileges: ALTER SYSTEM

The List Archive Status command lists the status of archiving for the current instance. Use this command to verify the status of the archive mode as well as find out current sequence numbers and the location that the archived redo logs are written to.

Command type: SQL*Plus

Line mode command: ARCHIVE LOG LIST

Required privileges: Capability to connect as INTERNAL, OSOPER, or OSDBA

SQL*DBA Backup Menu

Figure 7.9 shows the Backup menu for SQL*DBA. The options on this menu include Begin Online Tablespace Backup, End Online Tablespace Backup, Recover Database, Recover Tablespace, and Recover Data File.

Figure 7.9. Backup menu.

You use the backup options when you are running in ARCHIVELOG mode and you want to begin a "hot backup" of your data files.

To begin a tablespace backup, you must know the tablespace name. Once you issue the Begin Backup command, you can safely use your operating system commands to back up the data files that make up the tablespace.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace BEGIN BACKUP

Required privileges: ALTER TABLESPACE

Use the End Online Tablespace Backup option when you are finished with the backup of the tablespace.

Command type: SQL*Plus

Line mode command: ALTER TABLESPACE tablespace END BACKUP

Required privileges: ALTER TABLESPACE

It is usually not a good idea to set all your tablespaces to backup mode at the same time. If you have a system crash while a tablespace is in backup mode, you have to recover before you can bring the tablespace online again.

You use the Recover options to bring the database back to a usable state after some sort of database or system failure. Each option deals with a specific type of recovery. If you're running in ARCHIVELOG mode and all the archived redo log files needed for recovery are available, you can recover the database to its final state before the crash. Other recovery options provide recovery to the point where it is canceled by the operator, to a certain date and time specified by the operator, or to a specific change number. These options apply only to database and tablespace recovery. When you attempt to start an instance and recovery is required, the instance notifies you about which type of recovery is necessary.

You use Recover Database to recover the entire database. For example, you can restore all the data files and archived redo logs from tape after a serious disk failure. Once the data files and redo logs are recovered from media, you must use the recover commands to bring the database into a usable state.

Command type: SQL*DBA

Line mode command: RECOVER DATABASE

Required privileges: ALTER DATABASE

You can use Recover Tablespace if you have a single disk failure and must restore all a tablespace's data files from backup. Once the files are restored to their original locations, you can issue the recover command to have the database apply redo log information to bring the tablespace online. This recovery can take place while the database is online, but all tables in the given tablespaces are unavailable for use until recovery is complete.

Command type: SQL*DBA

Line mode command: RECOVER TABLESPACE

Required privileges: ALTER DATABASE

You must use Recover Data File if you have accidentally overwritten or erased a data file or if you've had a form of media failure that does not affect all the data files in a particular tablespace. Once the data file is restored, you can issue the RESTORE DATAFILE command and then bring the tablespace back online.

Command type: SQL*DBA

Line mode command: RECOVER DATAFILE

Required privileges: ALTER DATABASE

SQL*DBA Security Menu

You use the Security menu to define how and to whom you grant access to the database and its data. Through your work with user accounts, you can audit and limit data access and resource utilization. Profiles limit the amount of resources that are available to certain individuals. Roles enable you to manage table and object access by creating groups (roles), granting access to those groups, and then giving access to the group to specific users. Figure 7.10 shows the Security menu.

Figure 7.10. Security menu.

You can use the Create, Alter, and Drop User options to speed the creation or deletion of a user in the database. From these menus, you have the capability to manipulate all aspects of a user's definition.

Create User creates a database user and specifies all the parameters needed to begin working in that account. Specific information includes the user name, type of authentication, default and temporary tablespaces, quota limit, and profile (if any).

Command type: SQL*Plus

Line mode command: CREATE USER

Required privileges: CREATE USER

Alter User is nearly identical to Create User except for the Roles option. From here, you can define a default role for the user you are modifying. Use this option to change a user's characteristics.

Command type: SQL*Plus

Line mode command: ALTER USER

Required privileges: ALTER USER

You use Drop User to remove a user and possibly all tables owned by that user from the database. In order to remove a user, you must drop all the objects owned by that user either manually or with the CASCADE option.

Command type: SQL*Plus

Line mode command: DROP USER

Required privileges: DROP USER

You use the Create, Alter, and Drop Role options to define a role and how it is authenticated. You must use the grant options from the Security menu to grant table access to role names or to grant roles to users.

Command type: SQL*Plus

Line mode command: CREATE ROLE

Required privileges: CREATE ROLE

You use Alter Role to change the type of authentication for a given role. You can create roles to require passwords before a user is granted the role's access definitions.

Command type: SQL*Plus

Line mode command: ALTER ROLE

Required privileges: ALTER ANY ROLE

Using Drop Role removes the role name and all references to that role from the database. Any users who had this role granted to them will effectively have that role revoked.

Command type: SQL*Plus

Line mode command: DROP ROLE

Required privileges: DROP ANY ROLE

Profiles enable you to limit the use of specific system and database resources. There are nine limitations you can set, and a user can belong to only one profile. If a user is not defined with a profile, there are no limitations to the amount of resources available to that user. This is the default.

CREATE PROFILE creates a profile with the name and limits you specify. For resource limits not specified in the creation of the profile, the database uses its own default value, which is usually unlimited unless you change resource costs. You can modify resource costs from the Security menu by using the option Alter Resource Cost.

Command type: SQL*Plus

Line mode command: CREATE PROFILE

Required privileges: CREATE PROFILE

Alter Profile works in the same way as Create Profile, but you are given a list of the values for existing profiles. You must first select an existing profile before you can modify it.

Command type: SQL*Plus

Line mode command: ALTER PROFILE

Required privileges: ALTER PROFILE

It is important to note that when you're altering a profile and you have selected the profile from the menu, although a list of standard values is provided, they do not reflect the current values for the profile. The only way to find the current values for the profile is to query SYS.PROFILE table or the DBA_PROFILES view.

You use Drop Profile to remove all traces of the profile from the database. Unlike with dropping roles, you must specify the CASCADE option if a user has been assigned the profile you want to drop.

Command type: SQL*Plus

Line mode command: DROP PROFILE

Required privileges: DROP PROFILE

For each resource listed in a profile, there is a resource cost associated with it. Oracle uses a formula to calculate each resource's cost in order to limit usage as defined in the profile. There are four resources that the DBA can modify: CPU Time/Session, Connect Time, Logical Reads/Session, and Private SGA/Session. (Private SGA is for instances running in Multi-Threaded Server mode.) The default value for each resource is 0. These costs are actually weights. If a site decides that CPU should be weighted higher in resources than disk I/O, you can start the menu or enter the command from the command line and enter a higher value (5 for instance), which would weight CPU usage higher than disk I/O in resource cost. This is helpful for systems with slower CPUs or possibly slower disk access. By limiting resource usage, you can gain greater control over how the system is used.

Command type: SQL*Plus

Line mode command: ALTER RESOURCE COST

Required privileges: ALTER RESOURCE COST

You use Grant and Revoke to limit access to data within the database. You can grant a role to users and grant access to objects to roles.

Command type: SQL*Plus

Line mode command: GRANT

Required privileges: GRANT ANY ROLE or ADMIN access to the role being granted.

Be careful with the ADMIN option. Users granted a role with the ADMIN option can grant that role to other users that you might not want to have that level of access.

Revoke removes access to the specified role or object.

Command type: SQL*Plus

Line mode command: REVOKE

Required privileges: GRANT ANY ROLE or ADMIN access to the role being revoked.

SQL*DBA Monitor Menu

The Monitor menu, which is shown in Figure 7.11, enables the developer or DBA (based on database privileges) to monitor various aspects of the database, such as aspects of the Multi-Threaded Server, sessions connected to the database, table access, SQL statements cached in the SGA, locks, file I/O, and so on. The Monitor option is not available in any other package supplied by Oracle (with the exception of Server Manager, Oracle's X-Windows replacement for SQL*DBA).

Figure 7.11. Monitor menu.

Privileges to run the monitor commands are based on access to views created by the CATALOG.SQL file and access to various performance-based tables and views owned by SYS. If you are having problems using the monitoring commands and you have access to these tables and views, make sure that you have issued a CONNECT INTERNAL command and have successfully connected to the database. If you still have problems, you should contact Oracle support for assistance.

The Multi-Threaded option monitors shared servers, dispatchers, circuits, and queues. The options help you correctly configure the number of shared servers and dispatchers required for your environment. Too many shared servers and you are using excess memory; too few and users begin to see unnecessary wait times.

Monitoring the shared servers enables you to filter the monitor output based on server name or the status. Keep a close eye on status, total idle time, and load. If load is high and idle time is high, you might experience system bottleneck (CPU or memory).

Command type: SQL*DBA

Line mode command: MONITOR SHARED

Required privileges: Access to V$SHARED_SERVER

When monitoring dispatchers, you get up-to-date information on how each dispatcher is running. Keep an eye out for load and status. If the load is too high, the dispatcher might be having trouble keeping up with the requests it has to send out to the servers. Adding more dispatchers might alleviate the problem.

Command type: SQL*DBA

Line mode command: MONITOR DISPATCHER

Required privileges: Access to V$DISPATCHER

Monitoring circuits shows the virtual circuits that are related to the shared servers. These circuits are the means by which users are connected to the database. This screen links a user back to a shared server and dispatcher.

Command type: SQL*DBA

Line mode command: MONITOR CIRCUIT

Required privileges: Access to V$CIRCUIT, V$DISPATCHER, V$SESSION, V$SHARED_SERVER

Monitoring queues gives you a look at the queue of requests that are handled by the Multi-Threaded Server. If the average wait time is high, you might need to add more dispatchers.

Command type: SQL*DBA

Line mode command: MONITOR QUEUE

Required privileges: Access to V$QUEUE

Choosing the Process option gives you valuable information on the user and instance processes currently running. One of the key portions of this screen is Latch Waited. If a user is waiting on a latch, you could be having resource problems.

Command type: SQL*DBA

Line mode command: MONITOR PROCESS

Required privileges: Access to V$PROCESS

The Session monitor gives you information on each session connected to the database. Key points of the session output are session ID and process ID (used for kill session commands) and lock waited. If a user is waiting on a lock, his session appears to hang.

Command type: SQL*DBA

Line mode command: MONITOR SESSION

Required privileges: Access to V$PROCESS, V$SESSION

If you choose to monitor a Table, you can find out the level of I/O that each table incurs. This is extremely important in alleviating disk bottlenecks. If possible, you should move I/O intensive tables to separate disks from each other.

Command type: SQL*DBA

Line mode command: MONITOR TABLE

Required privileges: Access to V$ACCESS

Monitoring the SQL Area helps you tune your custom applications. Because SQL is case sensitive, two identical SQL statements that differ only by a single uppercase or lowercase character are parsed and cached separately.

Command type: SQL*DBA

Line mode command: MONITOR SQLAREA

Required privileges: Access to V$SQLAREA

Library Cache monitoring is important because it can tell you how well your SGA is sized. It is important to keep an eye on the name space and the hit ratios associated with them. If the hit ratios are low, the database is forced to reparse and recache SQL statements back into the SGA.

Command type: SQL*DBA

Line mode command: MONITOR LCACHE

Required privileges: Access to V$LIBRARYCACHE

Latches are similar to locks, but their life spans are very short. The Latch option enables you to monitor the types of latches and their statistics.

Command type: SQL*DBA

Line mode command: MONITOR LATCH

Required privileges: Access to V$LATCH, V$LATCHHOLDER, V$LATCHNAME

Locking problems can be extremely difficult to resolve, especially in custom applications. Using the Lock monitor shows you who has locks and who is requesting them. If a process has Mode Requested listed on the right-hand side for a long period of time, you might be experiencing locking problems. It means that the user is requesting some sort of lock on the object, but the object is locked by another user. It is not uncommon for users to wait on locks, but depending on the wait time, it might warrant further investigation.

Command type: SQL*DBA

Line mode command: MONITOR LOCK

Required privileges: Access to V$LOCK, V$PROCESS, V$SESSION

File I/O monitoring can be helpful in finding data files that have high I/O rates associated with them. Knowing which tablespaces and data files have the highest access rates is very helpful in eliminating disk contention.

Command type: SQL*DBA

Line mode command: MONITOR FILEIO

Required privileges: Access to V$DBFILE, V$FILESTAT

If the database is running abnormally slow, it is likely that a user is running a large, I/O-intensive query. Monitoring System I/O is extremely useful in identifying users or processes with high I/O rates.

Command type: SQL*DBA

Line mode command: MONITOR SYSTEMIO

Required privileges: Access to V$PROCESS, V$SESSION, V$SESSTAT

You should monitor Rollback segments to help reduce rollback segment contention. It is also important to notice the number of active transactions in each rollback segment. There is a database parameter that defines how many active transactions are allowed per rollback segment. If this limit is reached, an error can result. It is also important to see how the rollback segment is extending and shrinking and how many extents each segment has.

Command type: SQL*DBA

Line mode command: MONITOR ROLLBACK

Required privileges: Access to V$ROLLNAME, V$ROLLSTAT

Session and system statistics are gathered by session ID and overall database statistics. You can do each type of monitoring on a combination of user, redo, enqueue, cache, parallel server, and SQL statistics.

Session statistics are based on each session connected to the database.

Command type: SQL*DBA

Line mode command: MONITOR SESSIONSTATISTIC

Required privileges: Access to V$SESSTAT, V$SYSSTAT

System statistics are based on the current instance.

Command type: SQL*DBA

Line mode command: MONITOR SYSTEMSTATISTIC

Required privileges: Access to V$SYSSTAT

SQL*DBA Help Menu

The Help menu is designed to give the user brief help on the commands and topics listed within the menu. Intended to be a brief introduction to the topics, the menu refers you to various Oracle manuals for further assistance. Figure 7.12 shows the Help menu.

Figure 7.12. Help menu.

The About SQL*DBA option gives you information on version numbers of some of the products associated with your current version of the Oracle instance. There is no command associated with this option.

Show Keys gives you a menu of keys and their functions.

Command type: SQL*DBA

Line mode command: HELP

Required privileges: None

SQL*DBA Command Reference

The following list contains SQL*DBA specific commands and their syntax. For more information on SQL*Plus specific commands, refer to Chapter 6, "SQL*Plus." The following commands are listed in alphabetical order.

ARCHIVE LOG

Description: The ARCHIVE LOG command controls the starting and stopping of the automatic archiving of used redo logs and displays the current status of archive logging.

Command syntax:

     ARCHIVE LOG

          LIST

          STOP

          START     TO  filename

          NEXT      TO  filename

          ALL       TO  filename

          integer   TO  filename

Keywords:

Examples:

     ARCHIVE LOG LIST;

     ARCHIVE LOG START;

     ARCHIVE LOG 10982 '/u01/app/oracle/admin/dev/arch/dev'

CONNECT

Description: You use the CONNECT command to connect to the database. Most SQL*DBA functions require that the user be connected before using them.

Command syntax:

     CONNECT username/password

     CONNECT username/password@instance

     CONNECT INTERNAL

Keywords:

Example:

     CONNECT INTERNAL;

     CONNECT SYSTEM/MANAGER;

     CONNECT SCOTT/TIGER;

DISCONNECT

Description: You use the DISCONNECT command to disconnect from the current instance without exiting SQL*DBA. It has no additional parameters. You can use this command in conjunction with SET INSTANCE to access multiple instances at your site without exiting and reentering SQL*DBA.

Command syntax:

     DISCONNECT

Example:

     DISCONNECT;

EXECUTE

Description: You use EXECUTE to execute a one-line PL/SQL statement. If you want to execute more than one line, you must use the BEGIN . . . END format for PL/SQL. You must also be connected to a database before executing.

Command syntax:

     EXECUTE PL/SQL statement

Example:

     EXECUTE total_orders;

EXIT

Description: EXIT is the command you use to exit the SQL*DBA session. It automatically disconnects you from the current database if a connection has been established. This command has no parameters or keywords.

Command syntax:

     EXIT

Example:

     EXIT;

HOST

Description: The HOST command executes an operating system command or program while you're still in SQL*DBA. This command shells you out of SQL*DBA for the duration of the command. If issued by itself, it shells you to the operating system until you type EXIT to return to SQL*DBA.

Command syntax:

     HOST operating system command

     HOST

Keywords:

operating system command A valid operating system command or program.

Examples:

     HOST who;

     HOST dir;

     HOST;

MONITOR

Description: The MONITOR command enables you to monitor various statistics and attributes of the database, processes, or users. This command is extremely helpful in analyzing and resolving database problems. You can specify any parameters that the menu requests in order on the command line.

Command syntax:

     MONITOR   CIRCUIT

               DISPATCHER

               FILEIO

               LATCH

               LCACHE

               LOCK

               PROCESS

               QUEUE

               ROLLBACK

               SESSION

               SESSIONSTATISTIC

               SHARED

               SQLAREA

               SYSTEMIO

               SYSTEMSTATISTIC

               TABLE

Keywords:

Examples:

     MONITOR PROCESS;

     MONITOR LATCH;

     MONITOR LOCK 10 20;

     MONITOR SYSTEMSTATISTIC;

PRINT

Description: The PRINT command prints the value of a variable that you defined using the SQL*Plus command VARIABLE.

Command syntax:

     PRINT variable

Keywords:

variable The name of the variable defined with the VARIABLE command.

Examples:

     PRINT COUNTER;

     PRINT     NAME;

RECOVER

Description: You use the RECOVER command to perform media recovery on data files, tablespaces, or entire databases as required. You must be connected as INTERNAL to use this command, and you must have a dedicated process. You cannot be connected through Oracle's Multi-Threaded Server.

Command syntax:

     RECOVER DATABASE

     RECOVER DATABASE UNTIL

                         CANCEL

                         CHANGE integer

                         TIME date

     RECOVER DATABASE USING BACKUP CONTROLFILE

     RECOVER TABLESPACE tablespace

     RECOVER DATAFILE filename

Keywords:

Examples:

     RECOVER TABLESPACE tools;

     RECOVER DATABASE;

     RECOVER DATAFILE 'users_01.dbf';

     RECOVER DATABASE UNTIL '1994-10-11:15:01:00';

     RECOVER TABLESPACE tools, users;

SET

Description: The SET command sets characteristics for the current SQL*DBA session. These characteristics are not saved for future sessions.

Command syntax:

     SET ARRAYSIZE integer

          AUTORECOVERY ON/OFF

          CHARWIDTH integer

          COMPATIBILITY V6/V7

          CYCLE integer

          DATEWIDTH integer

          ECHO ON/OFF

          FETCHROWS integer

          HISTORY integer

          INSTANCE instance-path/LOCAL

          LABWIDTH

          LINES integer

          LOGSOURCE pathname/DEFAULT

          LONGWIDTH integer

          MAXDATA integer

          NUMWIDTH integer

          RETRIES integer/INFINITE

          SERVER OUTPUT OFF/ON SIZE integer

          SPOOL filename/OFF

          STOPONERROR ON/OFF

          TERM PAGE/NOPAGE

          TERMOUT ON/OFF

          TIMING ON/OFF

Keywords:

Examples:

     SET INSTANCE D:DEV-PROD

     SET HISTORY 50;

     SET NUMWIDTH 20;

SHOW

Description: Using the SHOW command shows the values of all the parameters set by the SET command. Additionally, this command supports several other parameters listed in the Keywords section. For a definition of any parameter listed, refer to the previous section on SET.

Command Syntax:

SHOW ARRAYSIZE

          AUTORECOVERY

          CHARWIDTH

          COMPATIBILITY

          CYCLE

          DATEWIDTH

          ECHO

          FETCHROWS

          HISTORY

          INSTANCE

          LABWIDTH

          LINES

          LOGSOURCE

          LONGWIDTH

          MAXDATA

          NUMWIDTH

          RETRIES

          SERVER OUTPUT

          SPOOL

          STOPONERROR

          TERM

          TERMOUT

          TIMING

ALL

PARAMETERS

ERRORS

Keywords:

Examples:

     SHOW SGA;

     SHOW TERMOUT;

     SHOW ALL;

     SHOW PARAMETERS COUNT;

     SHOW ERRORS;

SHUTDOWN

Description: You use SHUTDOWN to stop a currently running database. Various options include closing or dismounting the database.

Command syntax:

     SHUTDOWN  ABORT      dbname

               IMMEDIATE  dbname

               NORMAL     dbname

Keywords:

Examples:

     SHUTDOWN;

     SHUTDOWN IMMEDIATE dev;

     SHUTDOWN ABORT;

SPOOL

Description: The SPOOL command begins or ends the spooling of command output to a specified file.

Command syntax:

     SPOOL OFF

     SPOOL filename

Keywords:

Examples:

     SPOOL ON 'kelly.spl';

     SPOOL OFF;

STARTUP

Description: You use STARTUP to start a database. With options, this command enables you to bring the database into various stages of use for maintenance. As with the SHUTDOWN command, you must be connected as internal, and you cannot be connected via Oracle's Multi-Threaded Server.

Command syntax:

     STARTUP

               FORCE

               RESTRICT

               PFILE=filename

               MOUNT

               NOMOUNT

          OPEN

               RECOVER

               database

               mount options

Keywords:

Examples:

     STARTUP;

     STARTUP MOUNT;

     STARTUP PFILE='/home/oracle/init.ora' PARALLEL;

     STARTUP MOUNT RESTRICT;

     STARTUP FORCE;

Summary

SQL*DBA is a powerful tool that is useful for creating, managing, and tuning all your Oracle instances. The flexibility to run in command, line, or menu mode gives the user a definite advantage that many other packages do not provide. The capability to automate many of the SQL*DBA commands enables the database administrator to turn over some of the database operations to secondary support personnel—which can help create a higher availability system with less down time.

Overall, this tool is valuable in the day-to-day responsibilities of every database administrator and custom developer.