SQL*Plus®
User’s Guide and Reference
Release 10.1
Part No. B12170-01
December 2003
SQL*Plus User’s Guide and Reference, Release 10.1
Part No. B12170-01
Copyright © 1996, 2003 Oracle Corporation. All rights reserved.
Primary Author: Simon Watt
Contributor: Alison Goggin, Alison Holloway, Christopher Jones, Luan Nim, Richard Rendell, Andrei
Souleimanian, Ian Wu.
The Programs (which include both the software and documentation) contain proprietary information;
they are provided under a license agreement containing restrictions on use and disclosure and are also
protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering,
disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability
with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. This document is not warranted to be
error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of
these Programs may be reproduced or transmitted in any form or by any means, electronic or
mechanical, for any purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the
Programs on behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical
data delivered to U.S. Government customers are "commercial computer software" or "commercial
technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific
supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the
Programs, including documentation and technical data, shall be subject to the licensing restrictions set
forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set
forth in FAR 52.227-19, Commercial Computer Software Restricted Rights (June 1987). Oracle
Corporation, 500 Oracle Parkway, Redwood City, CA 94065

Starting SQL*Plus Windows GUI xxxviii
Starting and Stopping the iSQL*Plus Application Server xxxix
Starting iSQL*Plus xxxix
Connecting to a Different Database xl
Sample Schemas and SQL*Plus xl
Running your first Query xli
Exiting SQL*Plus xlii
iv
Part I SQL*Plus Getting Started
1 SQL*Plus Overview
What is SQL*Plus 1-1
SQL*Plus Command-line and Windows GUI Architecture 1-2
SQL*Plus Client 1-2
Oracle Database 1-2
iSQL*Plus Architecture 1-2
Web Browser 1-3
Application Server 1-3
Oracle Database 1-3
SQL*Plus Installation 1-4
SQL*Plus Date Format 1-4
Who Can Use SQL*Plus 1-4
How Can I Learn SQL*Plus 1-5
How to Use the SQL*Plus Guide 1-5
Oracle Database Sample Schemas and SQL*Plus 1-5
Unlocking the Sample Tables 1-6
2 SQL*Plus User Interface
SQL*Plus Command-line User Interface 2-1
The Command-line Screen 2-1
Changing the Command-line Font and Font Size 2-2
Windows Graphical User Interface 2-3

Enabling SSL with iSQL*Plus 3-21
Enabling or Disabling iSQL*Plus or iSQL*Plus Help 3-24
Enabling User Defined HTML Markup 3-24
iSQL*Plus Web Browser Configuration 3-25
Session Integrity 3-25
Retained Session Settings 3-26
Windows Graphical User Interface Configuration 3-26
Setting Options and Values Using the Environment Dialog 3-26
Customizing Registry Entries that affect SQL*Plus on Windows 3-28
4 Starting SQL*Plus
Login Username and Password 4-1
Changing your Password 4-2
Changing Your Password in iSQL*Plus 4-2
Expired Password 4-4
vi
Expired Password Screen in iSQL*Plus 4-4
Connecting to a Database 4-4
Net Service Name 4-5
Full Connection Identifier 4-6
Easy Connection Identifier 4-6
Connectionless Session with /NOLOG 4-6
Starting SQL*Plus 4-7
Starting Command-line SQL*Plus 4-8
Getting Command-line Help 4-9
Starting the Windows Graphical User Interface 4-9
Starting the iSQL*Plus Application Server 4-11
To Check the HTTP Port used by the iSQL*Plus Application Server 4-12
Stopping the iSQL*Plus Application Server 4-13
Running iSQL*Plus 4-13
Running iSQL*Plus as a DBA 4-14

6 Using Scripts in SQL*Plus
Editing Scripts 6-2
Writing Scripts with a System Editor 6-2
Editing Scripts in SQL*Plus Command-Line 6-3
Listing the Buffer Contents 6-4
Editing the Current Line 6-5
Appending Text to a Line 6-7
Adding a New Line 6-8
Deleting Lines 6-9
Placing Comments in Scripts 6-9
Using the REMARK Command 6-10
Using /* */ 6-10
Using 6-10
Notes on Placing Comments 6-11
Running Scripts 6-13
Running a Script as You Start SQL*Plus 6-14
Nesting Scripts 6-15
Exiting from a Script with a Return Code 6-15
Defining Substitution Variables 6-16
Using Predefined Variables 6-16
Using Substitution Variables 6-17
Where and How to Use Substitution Variables 6-17
Avoiding Unnecessary Prompts for Values 6-20
viii
Restrictions 6-23
System Variables and iSQL*Plus Preferences 6-23
Substitution Variables in iSQL*Plus 6-24
iSQL*Plus Input Required Screen 6-25
Passing Parameters through the START Command 6-26
Communicating with the User 6-28

Setting the Top and Bottom Titles and Headers and Footers 7-24
Displaying System-Maintained Values in Titles 7-29
Listing, Suppressing, and Restoring Page Title Definitions 7-30
Displaying Column Values in Titles 7-31
Displaying the Current Date in Titles 7-32
Setting Page Dimensions 7-33
Storing and Printing Query Results 7-35
Creating a Flat File 7-35
Sending Results to a File 7-36
Sending Results to a Printer 7-36
8 Generating HTML Reports from SQL*Plus
Creating Reports using Command-line SQL*Plus 8-1
Creating Reports 8-2
Suppressing the Display of SQL*Plus Commands in Reports 8-6
HTML Entities 8-7
Creating Reports using iSQL*Plus 8-8
9 Tuning SQL*Plus
Tracing Statements 9-1
Controlling the Autotrace Report 9-2
Execution Plan 9-4
Statistics 9-4
Collecting Timing Statistics 9-8
Tracing Parallel and Distributed Queries 9-8
SQL*Plus Script Tuning 9-11
COLUMN NOPRINT 9-12
SET APPINFO OFF 9-12
SET ARRAYSIZE 9-12
SET DEFINE OFF 9-12
SET FLUSH OFF 9-12
SET LINESIZE 9-13

Introduction to Database Startup and Shutdown 11-2
Database Startup 11-2
Database Shutdown 11-3
Redo Log Files 11-4
ARCHIVELOG Mode 11-4
Database Recovery 11-5
xi
12 SQL*Plus Globalization Support
Configuring Globalization Support in Command-line SQL*Plus 12-2
SQL*Plus Client 12-2
Oracle Database 12-2
Configuring Multiple Language Support in iSQL*Plus 12-2
Web Browser 12-2
Application Server 12-3
NLS_LANG Environment Variable 12-3
Viewing NLS_LANG Settings 12-4
Setting NLS_LANG 12-5
Part III SQL*Plus Reference
13 SQL*Plus Command Reference
SQL*Plus Command Summary 13-2
@ ("at" sign) 13-6
@@ (double "at" sign) 13-8
/ (slash) 13-10
ACCEPT 13-11
APPEND 13-13
ARCHIVE LOG 13-14
ATTRIBUTE 13-17
BREAK 13-19
BTITLE 13-24
CHANGE 13-26

SET System Variable Summary 13-104
SET APPI[NFO] 13-107
SET ARRAY[SIZE] 13-109
SET AUTO[COMMIT] 13-109
SET AUTOP[RINT] 13-109
SET AUTORECOVERY 13-110
SET AUTOT[RACE] 13-110
SET BLO[CKTERMINATOR] 13-111
SET CMDS[EP] 13-111
SET COLSEP 13-112
SET COM[PATIBILITY] 13-113
SET CON[CAT] 13-114
SET COPYC[OMMIT] 13-114
SET COPYTYPECHECK 13-114
SET DEF[INE] 13-114
xiii
SET DESCRIBE 13-115
SET ECHO 13-116
SET EDITF[ILE] 13-116
SET EMB[EDDED] 13-116
SET ESC[APE] 13-117
SET FEED[BACK] 13-117
SET FLAGGER 13-118
SET FLU[SH] 13-118
SET HEA[DING] 13-118
SET HEADS[EP] 13-119
SET INSTANCE 13-119
SET LIN[ESIZE] 13-120
SET LOBOF[FSET] 13-120
SET LOGSOURCE 13-121

SET UND[ERLINE] 13-135
SET VER[IFY] 13-135
SET WRA[P] 13-135
SHOW 13-136
SHUTDOWN 13-142
SPOOL 13-144
START 13-146
STARTUP 13-148
STORE 13-152
TIMING 13-153
TTITLE 13-155
UNDEFINE 13-159
VARIABLE 13-160
WHENEVER OSERROR 13-168
WHENEVER SQLERROR 13-170
14 SQL*Plus Error Messages
SQL*Plus Error Messages 14-1
iSQL*Plus Error Messages 14-45
COPY Command Messages 14-54
xv
Part IV SQL*Plus Appendixes
A SQL*Plus Limits
B SQL*Plus COPY Command
COPY Command Syntax B-1
Copying Data from One Database to Another B-4
Copying Data between Tables on One Database B-9
C Obsolete SQL*Plus Commands
SQL*Plus Obsolete Command Alternatives C-1
BTI[TLE] text (obsolete old form) C-2
COL[UMN] {column|expr} DEF[AULT] (obsolete) C-2

Q Electronic mail: [email protected]
Q FAX: +61 3 9690 0043 Attn: Oracle SQL*Plus
Q Postal service:
Oracle Corporation Australia Pty Ltd
Oracle SQL*Plus Documentation
324 St Kilda Road
Melbourne, VIC 3004
Australia
If you would like a reply, please give your name, address, telephone number, and electronic mail
address (optional).
If you have problems with the software, please contact your local Oracle Support Services.
xviii
xix
Preface
The SQL*Plus (pronounced "sequel plus") User's Guide and Reference introduces
the SQL*Plus program and its uses. It also provides a detailed description of each
SQL*Plus command.
Throughout this document, unless explicitly stated otherwise, SQL*Plus is used to
refer to SQL*Plus behavior available through all its user interfaces: command-line,
Windows Graphical User Interface and the iSQL*Plus web-based user interface.
This preface contains these topics:
Q Intended Audience
Q Documentation Accessibility
Q Structure
Q Related Documents
Q Conventions
xx
Intended Audience
The SQL*Plus User's Guide and Reference is intended for business and technical users
and system administrators who perform the following tasks:

This document contains:
SQL*Plus Quick Start
A brief guide to get you up and running after installation.
PART I, SQL*Plus Getting Started
Provides an overview of SQL*Plus, describes the command-line interface, Windows
Graphical User Interface (GUI) and the iSQL*Plus user interface, and provides
configuration information and information you need to log in and run SQL*Plus.
Chapter 1, "SQL*Plus Overview"
An overview of SQL*Plus, SQL*Plus architecture and user interfaces, with
instructions on using this guide, and information on what you need to run
SQL*Plus.
Chapter 2, "SQL*Plus User Interface"
Describes the SQL*Plus command-line, Windows GUI and iSQL*Plus user
interfaces.
Chapter 3, "Configuring SQL*Plus"
Explains how to configure your SQL*Plus command-line, Windows GUI and
iSQL*Plus environments.
Chapter 4, "Starting SQL*Plus"
Provides command syntax and explanations for the SQLPLUS command, explains
how to start, connect to an Oracle database, access the command-line and online
help and exit SQL*Plus. It provides information about the login username and
password and the connection identifier you use to connect to an Oracle database.
PART II, Using SQL*Plus
Contains SQL*Plus user guide and tutorial content, information about writing
SQL*Plus scripts, and SQL*Plus tuning, security, database administration and
globalization information.
Chapter 5, "SQL*Plus Basics"
Explains how to enter and execute commands. You learn by following step-by-step
examples using sample tables.
xxii

Contains SQL*Plus Appendixes.
Appendix A, "SQL*Plus Limits"
Lists the maximum values for elements of SQL*Plus.
Appendix B, "SQL*Plus COPY Command"
Provides syntax and usage information for the COPY command.
Appendix C, "Obsolete SQL*Plus Commands"
Provides information on obsolete SQL*Plus commands.
Appendix D, "Commands Not Supported in iSQL*Plus"
Lists SQL*Plus commands that are not supported in iSQL*Plus.
Related Documents
For more information, see these Oracle resources:
Q SQL*Plus Quick Reference
Q PL/SQL User's Guide and Reference
Q Oracle Database SQL Reference
Q Oracle Database Concepts
Q Oracle Database Administrator's Guide
Q Oracle Database Backup and Recovery Basics
Q Oracle Database Application Developer's Guide - Fundamentals
Q Oracle XML DB Developer's Guide
Q Oracle Database Globalization Support Guide
Q Oracle Database Heterogeneous Connectivity Administrator's Guide
Q Oracle Database Error Messages
Q Oracle Database Upgrade Guide
Q Oracle Database Reference
Q Oracle Database Performance Tuning Guide
xxiv
Q Oracle Net Services Administrator's Guide
Q Pro*COBOL Programmer's Guide
Q Pro*C/C++ Programmer's Guide
Q Oracle Database installation and user's manuals for your operating system

Convention Meaning Example
Bold Bold typeface indicates terms that are
defined in the text or terms that appear in
a glossary, or both.
When you specify this clause, you create an
index-organized table.
Italics Italic typeface indicates book titles or
emphasis.
Oracle Database Concepts
Ensure that the recovery catalog and target
database do not reside on the same disk.
UPPERCASE
monospace
(fixed-width
font)
Uppercase monospace typeface indicates
elements supplied by the system. Such
elements include parameters, privileges,
datatypes, RMAN keywords, SQL
keywords, SQL*Plus or utility commands,
packages and methods, as well as
system-supplied column names, database
objects and structures, usernames, and
roles.
You can specify this clause only for a NUMBER
column.
You can back up the database by using the
BACKUP command.
Query the TABLE_NAME column in the USER_
TABLES data dictionary view.

methods.
lowercase
italic
monospace
(fixed-width
font)
Lowercase italic monospace font
represents placeholders or variables.
You can specify the
parallel_clause
.
Run
Uold_release
.SQL where
old_release

refers to the release you installed prior to
upgrading.