ORACLE SQL/PL-SQL/DBA NOTES
BY
Mohammad Sajid Farooq
Instructor,
PETROMAN TRAINING INSTITUTE
SUKKUR CAMPUS
Email Address: [email protected]
Database: A database in an organized collection of data serving a central purpose. It is organized in the sense that it contains data that is stored, formatted, accessed, and represented in a consistent manner. It serves as a central purpose in that it does not contains extraneous or superfluous data. A phone book is a good example of database.
Database management system: A Database Management System (DBMS) is the software that manages a database. It acts as a repository for all data and is responsible for its storage, security, integrity, concurrency, recovery, and access. The DBMS has a data dictionary, some time referred to as system catalog, which stores data about everything it holds, such as names, structures, locations, and types.
This data is also referred to as metadata, meaning data about data. The life span of a piece of data, from its creation to its deletion, is recorded in the data dictionary, as is all logical and physical information about that piece of data. A database Administrator (DBA) should become intimate with the data dictionary of the DBMS, which serves him or her over the life of the database.
Role Of Database Management System
Securing data: security is always a concern in a production database, and often in a development or test database too. It is usually not a question of whether or not to have any security, but rather how much to have. A DBMS typically offers several layers of security, in addition to the operating system (OS) and network security facilities. Most often, a DBMS holds user account with passwords requiring the user to login, Or be authenticated, in order to access the database.
DBMSs also offer other mechanisms, such as groups, roles, privileges, and profiles, which all offer a further refinement of security. These security levels not only provide for enforcement, but also for the establishment of business security policies. For example, only an authenticated user who belongs to an aviation group may access the aviation data. Or, only an authenticated user who has the role of operator may back up the database.
Integrity: the integrity of data refers to its consistency and correctness. For data to be consistent, it must be modeled and implemented the same way in all of its occurrences. For data to be correct, it must be right, accurate, and meaningful. One way a DBMS maintains integrity is by locking a data item in the process of being changed. A database usually locks at the database page level or at the row level. Incidentally, locking also permits concurrency.
Introduction to PL/SQL
What is PL/SQL?PL/SQL is the procedural language extensions to SQL Procedural programming language Uses detailed instructions Processes statements sequentially Combines SQL commands with procedural instructionsUsed to perform sequential processing using an Oracle database PL/SQL Procedural Language Constructs Variables and types Control Structures Sequence Selection: IF-THEN-ELSE Iteration: LOOP Procedures and Functions Object types and methods
PL/SQL Variables
Variable names are symbolic memory addresses (locations) that your program can use to store values. Variable names must follow the Oracle naming standard Can use reserved words (BEGIN, NUMBER) and table names for variable names, but is not a good practice
Make variable names descriptive Use lower-case letters, and separate words with
underscores Example: current_s_id
Declaring PL/SQL Variables
PL/SQL is a strongly-typed language All variables must be declared prior to use Syntax for declaring a variable:
variable_name data_type_declaration;
Example:
current_s_id NUMBER(6);
Variable Declaration Includes A data type that identifies the type of data that the variable will store An identifier that is the variable’s name An operational assigned (initial) value
An ending semicolon
PL/SQL Data Types (pp. 42-53)ScalarReferences a single valueCompositeReferences a data structureReferenceReferences a specific database itemLOBReferences a large binary objectScalar Data TypesDatabase scalar data types:
Non-database scalar data types:
Integers: BINARY_INTEGER, INTEGER, INT, SMALLINT
Decimal numbers: DEC, DECIMAL, DOUBLE, PRECISION,
NUMERIC, REAL,BOOLEAN
Composite Data Types
Reference multiple data elements, such as a record
Types:
RECORD
TABLE
VARRAY
Tabular structure that can expand or contract as needed
Reference Data Types
Reference a database item Assume data type of item
%TYPE: assumes data type of field
%ROWTYPE: assumes data type of entire row
PL/SQL Programs
Made up of blocks
Blocks can be nested within each other
Each block is a logical unit of work in a program
PL/SQL Block Structure
DECLARE
/* Declaration section: PL/SQL variables, types,
cursors, and local subprograms */
BEGIN
/* Executable section: procedural and SQL statements.
The only required section */
EXCEPTION
/* Exception handling section: error-handling
statemnets */
END;
Nested PL/SQL Program Blocks
An inner program block can be nested within an outer program block PL/SQL Program Lines
May span multiple text editor lines Each line ends with a semicolon Text is not case sensitive
Comment Statements
Add Comments to Your Program
/* Developer: <your name>
* Date: <current date>
* Program: <file name, chapter number>
* <brief description of the program
*/
Arithmetic Operators Assignment Statements Displaying PL/SQL Output in SQL*Plus
Displaying PL/SQL Program Output in SQL*Plus
First set serveroutput on
SET SERVEROUTPUT ON
Command to output data from a PL/SQL program in
SQL*Plus:
DBMS_OUTPUT.PUT_LINE(‘output string’);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Current Output:’);
END;
/
Executing a PL/SQL Program in SQL*Plus
Copy program code from Notepad to SQL*Plus
Type / to execute
PL/SQL Data Type Conversion Functions
TO_DATE: character string to DATE
TO_DATE(‘07/14/01’, ‘MM/DD/YY’);
TO_NUMBER: character string to NUMBER
TO_NUMBER(‘2’);
TO_CHAR: NUMBER or DATE to character string
TO_CHAR(2);
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH:MI’);
Character String Functions
Concatenating strings: joining 2 or more character strings into a single string
Concatenation operator: || s_first_name := ‘Sarah’
s_last_name := ‘Miller’
s_full_name := s_first_name || ‘ ’ || s_last_name
Write your first PL/SQL program
DECLARE
todaysDate DATE;
BEGIN
todaysDate := SYSDATE;
DBMS_OUTPUT.PUT_LINE('Today''s date is '||
to_char(todaysDate, 'MM/DD/YYYY'));
END;
/
PL/SQL Character String Functions
RTRIM: removes blank trailing spaces
cust_address := RTRIM(cust_address);
LENGTH: returns string length (number of characters)
address_length := LENGTH(cust_address);
UPPER, LOWER: changes characters to all upper or lower case
s_name := UPPER(s_name);
s_name := LOWER(s_name);
PL/SQL Character String Functions
INSTR: searches a string and looks for a matching
substring and returns its starting position
starting_position := INSTR(string_being_searched, <search_string>);
blank_position := INSTR(‘Sarah Miller’, ‘ ’);
PL/SQL Character String Functions
SUBSTR: extracts a specific number of characters from
a string, starting at a given point
extracted_string := SUBSTR(string_being_searched, starting_point, number_of_characters_to_extract);
s_first_name := SUBSTR(‘Sarah Miller’, 1,5);
Debugging PL/SQL Programs Syntax error
Does not follow language guidelines Causes a PLS- compile error
Examples:
Locating and Correcting Syntax Errors
Isolate the line that is causing the error This may be before or after the line that is flagged
by the compiler Comment out lines as necessary until program runs One error may cause several cascading errors, so re-run program after fixing each error
Locating and Fixing Logic Errors
Identify the output variable(s) that have the error.
Identify the inputs and calculations that contribute
to the error.
Display the values of the inputs using DBMS_OUTPUT commands.
Take a break and look at it again later.
Ask a fellow student for help.
Ask your instructor for help.
NULL Values in Assignment Statements Until a value is assigned to a variable, the
variable’s value is NULL Performing an arithmetic value on a NULL value always
results in a NULL value
Advice: Always initialize variable values
PL/SQL Selection Structures(pp. 63-67, Urman)
IF/END IF:
IF condition THEN
program statements
END IF;
IF/ELSE/END IF:
IF condition THEN
program statements
ELSE
alternate program statements
END IF;
More on PL/SQL Selection Structures
IF/ELSIF:
IF condition1 THEN
program statements;
ELSIF condition2 THEN
alternate program statements;
ELSIF condition3 THEN
alternate program statements;
. . .
ELSE
alternate program statements;
END IF;
PL/SQL Comparison Operators
Evaluating NULL Conditions in IF/THEN Structures
If a condition evaluates as NULL, then it is FALSE
How can a condition evaluate as NULL?
It uses a BOOLEAN variable that has not been
initialized
It uses any other variable that has not been
initialized
PL/SQL Loops
Loop: repeats one or more program statements multiple
times until an exit condition is reached
Pretest loop: exit condition is tested before program
statements are executed
Posttest loop: exit condition is tested after program
statements are executed
LOOP … EXIT Loop
LOOP
program statements
IF condition THEN
EXIT;
END IF;
more program statements
END LOOP;
LOOP … EXIT WHEN Loop
WHILE Loop
Numeric FOR Loop
Cursors (chapter 6, Urman)
Pointer to a server memory location
Contains information about a SQL command in a PL/SQL programCalled the command’s context area
Types of Cursors
Implicit Cursors
Created automatically every time you use an INSERT,
UPDATE, DELETE, or SELECT command
Doesn’t need to be declared Can be used to assign the output of a SELECT command to one or more PL/SQL variables Can only be used if query returns one and only one
record
Implicit Cursor Syntax
SELECT field1, field2, …
INTO variable1, variable2, …
FROM tablename
WHERE search_condition_that_will_
return_a_single_record;
Explicit Cursors
Must be declared in program DECLARE section Can be used to assign the output of a SELECT command to one or more PL/SQL variables Can be used if query returns multiple records or no records
Using an Explicit Cursor
Declare the cursor
Open the cursor
Fetch the cursor result into PL/SQL program variables
Close the cursor
1.Declaring an Explicit Cursor
DECLARE
CURSOR cursor_name IS SELECT_statement;
2.Opening an Explicit Cursor
OPEN cursor_name;
3.Fetching Explicit Cursor Records
FETCH cursor_name INTO variable_name(s);
4.Closing an Explicit Cursor
CLOSE cursor_name;
5.Processing an Explicit Cursor
LOOP ..EXIT WHEN approach:
OPEN cursor_name;
LOOP
FETCH cursor_name INTO variable_name(s);
EXIT WHEN cursor_name%NOTFOUND:
END LOOP;
CLOSE cursor_name;
Processing an Explicit Cursor
Cursor FOR Loop approach:
FOR variable_name(s) in cursor_name LOOP
additional processing statements;
END LOOP;
Using Reference Data Types in Explicit Cursor Processing
Declaring a ROWTYPE reference variable:
DECLARE
reference_variable_name cursor_name%ROWTYPE;
Referencing a ROWTYPE reference variable:
reference_variable_name.database_field_name
PL/SQL Table of Records
PL/SQL table that can store multiple values that are referenced by a key Usually used to store database records that need to be processed by a PL/SQL program Improves performance by limiting number of database retrievals
PL/SQL Exception Handling
All error handling statements are placed in the EXCEPTION program block
Exception handler: program command that provides information about an error, and suggest correction actions Predefined Exceptions
Exception Handler Syntax
For Predefined Exceptions
WHEN exception1_name THEN
exception handling statements;
WHEN exception2_name THEN
exception handling statements;
…
WHEN OTHERS THEN
exception handling statements;
Undefined Exceptions
User-Defined Exceptions
Errors that will not cause a run-time error, but will violate business rules Programmer creates a custom error message
Exception Handling in Nest Program Blocks
If an exception is raised and handled in an inner block, program execution resumes in the outer block Exception Handling in Nested Program Blocks
DECLARE
variable declarations
BEGIN
program statements
additional program statements
EXCEPTION
error handling statements
END;
Exception Handling in Nested Program Blocks Exceptions raised in inner blocks can be handled by exception handlers in outer blocks
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 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.
Command Purpose
DESCRIBE Describes tables and views
EXECUTE Executes PL/SQL blocks
PRINT Prints the value of a variable defined with the VARIABLE command
REMARK Denotes a comment or remark and prevents the interpreter from executing the line
SET Sets or modifies the characteristics of the current SQL*DBA session
SHOW Shows characteristics of the current SQL*DBA session
VARIABLE Defines a variable to be used within the current SQL*DBA session
Table 7.1. SQL*DBA commands available only through the command line.
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
Cancel
PF4
Cancel the current operation (includes closing windows)
Menu
K0
Move to the menu bar (0 on your numeric keypad)
Next Group
Tab
Set focus on output or input windows (cycles through them)
Show Keys
Ctrl+K or Esc,K
Display complete table of keys and their functions
Arrow Keys
Up, Down, Left, Right
Navigate through the input, output, and menu screens
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. 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.
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:
LIST
Displays the current status of archive logging. The output includes log mode, automatic archival, the archive
destination, the oldest online log sequence, the next sequence to archive, and the current sequence number. If the
oldest online log sequence and the current log sequence numbers are different, either automatic archiving has been
disabled or the previous log files have not been archived yet.
START
Enables automatic archiving, which is controlled by the ARCH process.
STOP
Disables automatic archiving.
NEXT
Manually archives the next redo groups that have not been archived.
ALL
Archives all redo groups that have not been archived.
integer
Archives the log file with the sequence number integer stored in it. If the sequence number is invalid, an error is
given.
filename
Refers to the instance specific destination file or device for the log files to be written to.
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:
username
The user ID to connect to. It must be a valid account.
password
The password the account is identified with.
instance
The instance name or connect string of the destination instance. This can be either the name of a database link,
an alias created in SQL*Net Version 2, or the direct connect string specifying driver:system:instance_name.
INTERNAL
A privileged login used for many higher level commands such as startup and shutdown. This login is an alias for
SYS.
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:
CIRCUIT
Displays current information on the virtual circuits owned by each shared server in a Multi-Threaded
Server environment.
DISPATCHER
Displays current information about a shared server's dispatcher processes in a Multi-Threaded Server
environment.
FILEIO
Displays read/write information for every database file associated with the current instance.
LATCH
Displays information on all current latches.
LCACHE
Displays current information on the library cache.
LOCK
Lists the current processes and the locks they are waiting on. Using ALL lists all locks being held by
current processes.
PROCESS
Monitors summary information for every process connected to the current instance.
QUEUE
Lists information on each shared server's message queues.
ROLLBACK
Shows activity on every active rollback segment in the instance.
SESSION
Displays active process information.
SESSIONSTATISTIC
Shows user session statistics for current user processes.
SHARED
Monitors shared server activity.
SQLAREA
Gives various statistics on the shared SQL area.
SYSTEMIO
Summarizes the read/write statistics for each Oracle process. This information is not precise but
instead is representative of relative distribution of I/O.
SYSTEMSTATISTIC
Shows system statistics for the current database.
TABLE
Displays table names of tables referenced in SQL statements that have been recently parsed and
reside in the shared SQL area.
Examples:
MONITOR PROCESS;
MONITOR LATCH;
MONITOR LOCK 10 20;
MONITOR SYSTEMSTATISTIC;
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:
DATABASE
Requests the recovery of an entire database. Will apply redo log files to all tablespaces needing media recovery.
UNTIL TIME date
Used to specify an incomplete RECOVER to a specific time. You must specify the date in the following format:
'YYYY-MM-DD:HH24:MI:SS'
YYYY is a four-digit year.
MM is a two-digit month.
HH24 is the time in 24-hour specification.
MI is minutes.
SS is seconds.
UNTIL CHANGE integer
Used to recover until a specific change number. This is very useful in restoring a tablespace where a table was accidentally dropped. integer must be a valid change number, and the redo logs must be available to Oracle.
UNTIL CANCEL
Specifies recovery should continue applying redo logs until the operator cancels the
operation. Recovery continues redo log by redo log until canceled.
USING BACKUP
CONTROLFILE
Tells the database to use a backup version of the control file instead of the primary one. This control file must be available to Oracle, or the command will fail.
TABLESPACE tablespace
Recovers the specified tablespace, or tablespaces. You can recover up to 16 of them in a single statement.
DATAFILE filename
Specifies a particular data file belonging to a tablespace that you want to restore. There is no limit to the number of data files you can recover in a given statement.
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:
ARRAYSIZE integer
Indicates the number of rows that are fetched from the database at one time. The default is 20, and the maximum is specific to the operating system you are running.
AUTORECOVERY
Tells the database to automatically apply all redo logs necessary to bring the database, tablespace, or data file to a usable state. When this is ON, the database begins recovery without requesting input from the operator. Log filenames are derived from the database parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. If the files cannot be located, operator input is requested.
CHARWIDTH integer
Defines the column width displayed for columns of type CHAR. The default is 80, and if no integer is specified, the parameter is reset to 80.
COMPATIBILITY V6/V7
Sets the SQL*DBA compatibility mode to either Version 6 or Version 7. This parameter affects how you specify columns of type CHAR, integrity constraint definitions, and the storage parameters for rollback segments.
CYCLE integer Used for the MONITOR command, sets the time that the monitor screens cycle in gathering statistics. The default is 5 seconds. The minimum is 1 second; maximum is 3600 seconds. The smaller the number, the higher the impact on the database.
DATEWIDTH integer
Sets the width for DATE data to be displayed. The default is 9, and if entered with no integer, it is reset to 9. The range of values for this parameter is operating-system specific.
ECHO ON/OFF
Enables echoing of commands that are executed from command files. The default is OFF. In this mode, only the output is displayed.
FETCHROWS integer
This parameter limits the number of rows that are returned by a database query. It can be very useful in returning only the first 20 or 10 rows from a database table. The default returns all rows that match the given criteria, and as with all other parameters, entering the SET command without an integer resets the value to all.
HISTORY integer
Sets the number of SQL*DBA commands saved in the history buffer. Any commands that reside in this buffer can be recalled and reexecuted using the Previous and Next Command options in menu mode. The default is 10 commands.
INSTANCE instance-path/LOCAL
Sets the instance name to where all SQL*DBA commands are applied. The instance-path is defined by a system node name and database name separated with a hyphen. A sample connect string is my_host-my_database. Issuing the command with no database definition resets the instance back to the local default instance.
LABWIDTH
Used strictly with Oracle's Trusted Server package.
LINES integer
Limits the number of lines the output window of SQL*DBA can store and recall. After reaching the limit, the lines at the beginning of the buffer are erased. The default value is 1000.
LOGSOURCE pathname/DEFAULT
Tells Oracle where to find archived redo logs to be used during a recovery session. Use this to set the location to a temporary location where redo logs have been restored.
LONGWIDTH integer
Tells SQL*DBA how to display LONG data. By default, the display is 80 characters only. Once again, the operating system defines the range of values for this parameter.
MAXDATA integer
Sets the maximum number of bytes that you can fetch from the database in a single SELECT statement. The default is 20,480 bytes (20K). Your operating system defines the maximum number for this parameter.
NUMWIDTH integer
Defines the length in characters that data types of NUMBER are displayed in. The default is 10, and the minimum and maximum values are operating-system dependent.
RETRIES integer/INFINITE
Used with the STARTUP command, this specifies how many times the startup command attempts to start the database. INFINITE means it tries until it succeeds or is canceled.
SERVER OUTPUT OFF/ON SIZE
integer Specifies the size of the message buffer, in bytes, that can accumulate at one time.This message buffer is used by the PUT() and PUT_LINE() commands.
SPOOL filename/OFF
Captures commands and output from the current session to a file. Specifying OFF
closes the previously opened file.
STOPONERROR ON/OFF
Tells SQL*DBA, when executing a command file, to stop if it encounters an error. If it finds one, the rest of the command file is not executed, and it returns control to the operating system.
TERM PAGE/NOPAGE
Tells SQL*DBA to display output one page at a time. The default, NOPAGE, scrolls all output to the output window. After that, you can navigate to the output window and scroll through the saved output.
TERMOUT ON/OFF
Controls the display of output from SQL commands to the output window. ON
enables display of the output whereas OFF disables the output. This is helpful if you're spooling output to files. The output is sent to the spool file but not thE terminal.
TIMING ON/OFF
Displays the parse, execute, and fetch times for every SQL statement executed. The default is OFF. This option is useful for establishing response times.
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:
ALL
Shows the values of all settings. Does not show ERRORS, PARAMETERS, or SGA, which must be displayed separately.
ERRORS
Shows all errors encountered during the last compilation of a function, procedure, or package. Output includes the line, column, and error message generated.
LABEL
This is a Trusted Oracle parameter.
PARAMETERS
Shows the current values for all database parameters specified in the startup files for the current instance.
Used alone, it displays all parameters. Used in conjunction with a parameter name, it shows the specific parameter. If a partial parameter name is used, the output includes all parameters that are similar.
SGA
Shows current information on the System Global Area for the connected instance.
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:
ABORT
Shuts down the database immediately, without checkpointing the database. This procedure immediately kills all active sessions without rolling back or committing transactions and then closes and dismounts the database. If you use this command, the database will require recovery.
IMMEDIATE
Shuts down the database by preventing new connections, terminating all existing sessions, and committing or rolling back current transactions and then checkpoints the database before dismounting and closing it. Media recovery is not required.
NORMAL
This is the default. It waits for currently connected users to disconnect, prevents new connections, checkpoints, and then closes and dismounts the database. No media recovery is required.
dbname
This is a Trusted Oracle parameter that you should not use for normal or parallel operations.
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:
OFF
Closes the currently opened file.
filename
The file to spool output to.
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:
FORCE
Issues a shutdown abort of the current instance and then attempts to
start the instance again. This is sometimes required if there were
shutdown errors.
RESTRICT
Same as the ALTER SYSTEM ENABLE RESTRICTED
SESSION command; enables the database to start up in restricted
mode and will only give access to users with the RESTRICTED
SESSION role.
PFILE=filename
Enables the database to start up with a specific parameter file
(INIT.ORA). Very useful if the INIT.ORA file is not in the current
directory or if you are starting up a new database.
MOUNT
Mounts the database but does not open it for use.
NOMOUNT
Does not mount the database. You cannot use this option with the
MOUNT, OPEN, PARALLEL, SHARED, or EXCLUSIVE
options.
OPEN
Default option; mounts and opens the default database.
RECOVER
Similar to the RECOVER DATABASE command; starts the current
instance and recovers the database, if required. The recovery works
as if AUTORECOVERY were set to ON. If the recovery fails, the
database remains mounted but does not open.
database
Starts this specific instance. If no instance is specified, startup runs on
the default instance.
mount options
Specifies three types of options for the startup:
EXCLUSIVE
Mounts and opens the database for
a single instance to use.
PARALLEL
Mounts and opens the database for
parallel mode.
SHARED
Another name for PARALLEL.
RETRIES
Specifies the number of retries
before failing.
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.