SQL*Plus User’s Guide and Reference

✍ Scribed by Simon Watt

• Tongue: English
• Leaves: 478
• Edition: 10.2
• Category: Library

✦ Table of Contents

Contents
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What’s New in SQLPlus?
New Features in SQLPlus Release 10.2
SQLPlus Quick Start
SQLPlus Resources
SQLPlus Overview
SQLPlus Prerequisites
Starting SQLPlus Command-line
Starting SQLPlus Windows GUI
Starting iSQLPlus
Starting SQLPlus Instant Client
Connecting to a Different Database
Sample Schemas and SQLPlus
Running your first Query
Exiting SQLPlus
Part I SQLPlus Getting Started
1 SQLPlus User Interface
SQLPlus Command-line User Interface
The Command-line Screen
Changing the Command-line Font and Font Size
To Change the Command-line Interface Font and Font Size
Using a Special Character in Windows
Windows Graphical User Interface
Using the Mouse to Copy Text to the Command Prompt
Using Command Keys
Using the Windows GUI Menus
File Menu
Edit Menu
Search Menu
Options Menu
Help Menu
Changing the Windows GUI Font and Font Size
To Change the Windows GUI Font and Font Size
Using a Special Character
iSQLPlus User Interface
iSQLPlus Navigation
Icons
Tabs
Menus
Footer Links
iSQLPlus Login Screen
Username:
Password:
Connection Identifier:
Login
iSQLPlus DBA Login Screen
User Name
Password
Username:
Password:
Connection Identifier:
Privilege:
Login
iSQLPlus Workspace
Clear
Execute
Load Script
Save Script
Cancel
Next Page
iSQLPlus DBA Workspace
iSQLPlus History Screen
Script
Load
Delete
iSQLPlus Input Required Screen
Enter Value for sortcol
Continue
Cancel
iSQLPlus Preferences Screen
Cancel
Apply
Interface Configuration
System Configuration
Script Formatting
Script Execution
Database Administration
Change Password
Preferences and Equivalent SET Commands
2 Configuring SQLPlus
SQLPlus and iSQLPlus Environment Variables
SQLPlus and iSQLPlus Configuration
Site Profile
Default Site Profile Script
User Profile
Modifying Your LOGIN File
Storing and Restoring SQLPlus System Variables
Restoring the System Variables
Installing Command-line Help
Running the helpins Shell Script or Batch File to Install Command-line Help
Running the hlpbld.sql Script to Install Command-line Help
Running the helpdrop.sql Script to Remove Command-line Help
Configuring Oracle Net Services
iSQLPlus Application Server Configuration
Changing the iSQLPlus Application Server Port in Use
Testing if the iSQLPlus Application Server is Running
Setting the Level of iSQLPlus Logging
Setting the Session Time Out
Enabling Restricted Database Access
Enabling iSQLPlus DBA Access
Create Users
List Users
Grant Users the webDba Role
Remove Users
Revoke the webDba Role
Change User Passwords
Test iSQLPlus DBA Access
Enabling SSL with iSQLPlus
1. Generate Keys and Storage File
2. Load Root Certificate into Storage File
3. Request Certificate from CA
4. Import Certificate into Storage File
5. Configure iSQLPlus to run in SSL mode
Enabling iSQLPlus or iSQLPlus Help
Enabling Unauthorised Access Banner
Enabling URL Access
Enabling User Defined HTML Markup
iSQLPlus Web Browser Configuration
Session Integrity
Retained Session Settings
Windows Graphical User Interface Configuration
Setting Options and Values Using the Environment Dialog
Customizing Registry Entries that affect SQLPlus on Windows
Using the Registry
SQLPATH Registry Entry
SQLPLUS_FONT Registry Entry
SQLPLUS_FONT_SIZE Registry Entry
3 Starting SQLPlus
Login Username and Password
Secure External Password Store
Expired Password
Expired Password Screen in iSQLPlus
Changing your Password
Changing Your Password in iSQLPlus
Username:
Old password:
New password:
Retype new password:
Apply
Cancel
Connecting to a Database
Net Service Name
Full Connection Identifier
Easy Connection Identifier
Connectionless Session with /NOLOG
Starting SQLPlus
Starting Command-line SQLPlus
Getting Command-line Help
Starting the Windows Graphical User Interface
Starting the GUI from the Windows Menu
Starting the GUI from the Windows Command Prompt
Starting the iSQLPlus Application Server
To Check the HTTP Port used by the iSQLPlus Application Server
Stopping the iSQLPlus Application Server
Starting iSQLPlus
Starting iSQLPlus as a DBA
Starting iSQLPlus from a URL
Examples
Getting Help in iSQLPlus
Exiting SQLPlus
Exiting the Command-line User Interface
Exiting the Windows Graphical User Interface
Exiting the iSQLPlus User Interface
SQLPLUS Program Syntax
Options
COMPATIBILITY Option
HELP Option
VERSION Option
LOGON Option
MARKUP Options
MARKUP Usage Notes
RESTRICT Option
SILENT Option
Logon
Start
Part II Using SQLPlus
4 SQLPlus Basics
Entering and Executing Commands
The SQL Buffer
Executing Commands
Listing a Table Definition
Listing PL/SQL Definitions
Running SQL Commands
Understanding SQL Command Syntax
Dividing a SQL Command into Separate Lines
Ending a SQL Command
Running PL/SQL Blocks
Creating Stored Procedures
Running SQLPlus Commands
Understanding SQLPlus Command Syntax
Continuing a Long SQLPlus Command on Additional Lines
System Variables that Affect How Commands Run
Stopping a Command while it is Running
Running Operating System Commands
Pausing the Display
Saving Changes to the Database Automatically
Interpreting Error Messages
5 Using Scripts in SQLPlus
Editing Scripts
Writing Scripts with a System Editor
Editing Scripts in SQLPlus Command-Line
Listing the Buffer Contents
Editing the Current Line
Appending Text to a Line
Adding a New Line
Deleting Lines
Placing Comments in Scripts
Using the REMARK Command
Using /.../
Using - -
Notes on Placing Comments
Running Scripts
Running a Script as You Start SQLPlus
Nesting Scripts
Exiting from a Script with a Return Code
Defining Substitution Variables
Using Predefined Variables
Using Substitution Variables
Where and How to Use Substitution Variables
Avoiding Unnecessary Prompts for Values
Restrictions
System Variables and iSQLPlus Preferences
Substitution Variables in iSQLPlus
iSQLPlus Input Required Screen
Enter Value for sortcol
Continue
Cancel
Passing Parameters through the START Command
Communicating with the User
Receiving a Substitution Variable Value
Customizing Prompts for Substitution Variable
Sending a Message and Accepting Return as Input
Clearing the Screen
Using Bind Variables
Creating Bind Variables
Referencing Bind Variables
Displaying Bind Variables
Using REFCURSOR Bind Variables
6 Formatting SQLPlus Reports
Formatting Columns
Changing Column Headings
Default Headings
Changing Default Headings
Formatting NUMBER Columns
Default Display
Changing the Default Display
Formatting Datatypes
Default Display
Changing the Default Display
Copying Column Display Attributes
Listing and Resetting Column Display Attributes
Suppressing and Restoring Column Display Attributes
Printing a Line of Characters after Wrapped Column Values
Clarifying Your Report with Spacing and Summary Lines
Suppressing Duplicate Values in Break Columns
Inserting Space when a Break Column's Value Changes
Inserting Space after Every Row
Using Multiple Spacing Techniques
Listing and Removing Break Definitions
Computing Summary Lines when a Break Column's Value Changes
Computing Summary Lines at the End of the Report
Computing Multiple Summary Values and Lines
Listing and Removing COMPUTE Definitions
Defining Page and Report Titles and Dimensions
Setting the Top and Bottom Titles and Headers and Footers
Positioning Title Elements
Indenting a Title Element
Entering Long Titles
Displaying System-Maintained Values in Titles
Listing, Suppressing, and Restoring Page Title Definitions
Displaying Column Values in Titles
Displaying the Current Date in Titles
Setting Page Dimensions
Storing and Printing Query Results
Creating a Flat File
Sending Results to a File
Sending Results to a Printer
7 Generating HTML Reports from SQLPlus
Creating Reports using Command-line SQLPlus
Creating Reports
Suppressing the Display of SQLPlus Commands in Reports
HTML Entities
Creating Reports using iSQLPlus
8 Tuning SQLPlus
Tracing Statements
Controlling the Autotrace Report
Execution Plan
Statistics
Collecting Timing Statistics
Tracing Parallel and Distributed Queries
Execution Plan Output in Earlier Databases
SQLPlus Script Tuning
COLUMN NOPRINT
SET APPINFO OFF
SET ARRAYSIZE
SET DEFINE OFF
SET FLUSH OFF
SET LINESIZE
SET LONGCHUNKSIZE
SET PAGESIZE
SET SERVEROUTPUT
SET SQLPROMPT
SET TAB
SET TERMOUT
SET TRIMOUT ON SET TRIMSPOOL ON
UNDEFINE
9 SQLPlus Security
PRODUCT_USER_PROFILE Table
Creating the PUP Table
PUP Table Structure
Description and Use of PUP Columns
PUP Table Administration
Disabling SQLPlus, SQL, and PL/SQL Commands
Creating and Controlling Roles
Disabling SET ROLE
Disabling User Roles
Disabling Commands with SQLPLUS -RESTRICT
Program Argument Security
iSQLPlus Security
Enabling SSL with iSQLPlus
Administration Privileges
Enabling DBA Access
Enabling or Disabling Restricted Database Access
Security Usage Notes
10 Database Administration with SQLPlus
Overview
Introduction to Database Startup and Shutdown
Database Startup
Database Shutdown
Redo Log Files
ARCHIVELOG Mode
Database Recovery
11 SQLPlus Globalization Support
Configuring Globalization Support in Command-line SQLPlus
SQLPlus Client
Oracle Database
Configuring Multiple Language Support in iSQLPlus
Web Browser
Application Server
NLS_LANG Environment Variable
Viewing NLS_LANG Settings
Setting NLS_LANG
Part III SQLPlus Reference
12 SQLPlus Command Reference
SQLPlus Command Summary
@ ("at" sign)
@@ (double "at" sign)
/ (slash)
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
Predefined Variables
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INPUT
LIST
PASSWORD
PAUSE
PRINT
PROMPT
RECOVER
REMARK
REPFOOTER
REPHEADER
RUN
SAVE
SET
SET System Variable Summary
SET APPI[NFO]{ON | OFF | text}
SET ARRAY[SIZE] {15 | n}
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
SET AUTOP[RINT] {ON | OFF}
SET AUTORECOVERY [ON | OFF]
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
SET CMDS[EP] {; | c | ON | OFF}
SET COLSEP { | text}
SET CON[CAT] {. | c | ON | OFF}
SET COPYC[OMMIT] {0 | n}
SET COPYTYPECHECK {ON | OFF}
SET DEF[INE] {& | c | ON | OFF}
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
SET ECHO {ON | OFF}
SET EDITF[ILE] file_name[.ext]
SET EMB[EDDED] {ON | OFF}
SET ESC[APE] {\ | c | ON | OFF}
SET FEED[BACK] {6 | n | ON | OFF}
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
SET FLU[SH] {ON | OFF}
SET HEA[DING] {ON | OFF}
SET HEADS[EP] { | | c | ON | OFF}
SET INSTANCE [instance_path | LOCAL]
SET LIN[ESIZE] {80 | n} SET LIN[ESIZE] {150 | n} in iSQLPlus
SET LOBOF[FSET] {1 | n}
SET LOGSOURCE [pathname]
SET LONG {80 | n}
SET LONGC[HUNKSIZE] {80 | n}
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
SET NEWP[AGE] {1 | n | NONE}
SET NULL text
SET NUMF[ORMAT] format
SET NUM[WIDTH] {10 | n}
SET PAGES[IZE] {14 | n}
SET PAU[SE] {ON | OFF | text}
SET RECSEP {WR[APPED] | EA[CH] | OFF}
SET RECSEPCHAR { | c}
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
SET SHOW[MODE] {ON | OFF}
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
SET SQLCO[NTINUE] {> | text}
SET SQLN[UMBER] {ON | OFF}
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
SQLPlus Compatibility Matrix
SET SQLPRE[FIX] {# | c}
SET SQLP[ROMPT] {SQL> | text}
SET SQLT[ERMINATOR] {; | c | ON | OFF}
SET SUF[FIX] {SQL | text}
SET TAB {ON | OFF}
SET TERM[OUT] {ON | OFF}
SET TI[ME] {ON | OFF}
SET TIMI[NG] {ON | OFF}
SET TRIM[OUT] {ON | OFF}
SET TRIMS[POOL] {ON | OFF}
SET UND[ERLINE] {- | c | ON | OFF}
SET VER[IFY] {ON | OFF}
SET WRA[P] {ON | OFF}
SET XQUERY BASEURI {text}
SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}
SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}
SET XQUERY CONTEXT {text}
SHOW
SHUTDOWN
SPOOL
START
STARTUP
STORE
TIMING
TTITLE
UNDEFINE
VARIABLE
WHENEVER OSERROR
WHENEVER SQLERROR
XQUERY
13 SQLPlus Error Messages
SQLPlus Error Messages
iSQLPlus Error Messages
COPY Command Messages
Part IV SQLPlus Appendixes
A SQLPlus Limits
B SQLPlus COPY Command
COPY Command Syntax
Terms
Usage
Examples
Copying Data from One Database to Another
Understanding COPY Command Syntax
Controlling Treatment of the Destination Table
Interpreting the Messages that COPY Displays
Specifying Another User's Table
Copying Data between Tables on One Database
C Obsolete SQLPlus Commands
SQLPlus Obsolete Command Alternatives
BTI[TLE] text (obsolete old form)
COL[UMN] {column|expr} DEF[AULT] (obsolete)
DOC[UMENT] (obsolete)
NEWPAGE [1|n] (obsolete)
SET BUF[FER] {buffer|SQL} (obsolete)
SET COM[PATIBILITY]{V7 | V8 | NATIVE} (obsolete)
SET CLOSECUR[SOR] {ON|OFF} (obsolete)
SET DOC[UMENT] {ON|OFF} (obsolete)
SET MAXD[ATA] n (obsolete)
SET SCAN {ON|OFF} (obsolete)
SET SPACE {1|n} (obsolete)
SET TRU[NCATE] {ON|OFF} (obsolete)
TTI[TLE] text (obsolete old form)
D Commands Not Supported in iSQLPlus
E SQLPlus Instant Client
Choosing the SQLPlus Instant Client to Install
Basic Instant Client
Lightweight Instant Client
Lightweight SQLPlus Instant Client Error with Unsupported Character Set
Installing SQLPlus Instant Client by Downloading from OTN
Installing SQLPlus Instant Client from Linux RPM Packages
Installing SQLPlus Instant Client from the UNIX or Windows Zip Files
List of Files Required for SQLPlus Instant Client
Installing SQLPlus Instant Client from the 10g Client Release Media
Installing SQLPlus Instant Client on UNIX or Linux
Installing SQLPlus Instant Client on Windows
Configuring SQLPlus Instant Client
Configuring SQLPlus Instant Client on Linux (from RPMs)
Configuring SQLPlus Instant Client on Linux (from Client Media or Zip File) and UNIX
Configuring SQLPlus Instant Client on Windows
Connecting to a Database with SQLPlus Instant Client
AS SYSDBA or AS SYSOPER Connections with SQLPlus Instant Client
Uninstalling Instant Client
Uninstalling SQLPlus Instant Client
Uninstalling the Complete Instant Client
Index
Symbols
Numerics
A
B
C
D
E
F
G
H
I
J
L
M
N
O
P
Q
R
S
T
U
V
W
X