Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Ac cessibility

Structure

Related Documents

Conventions

What's New in SQL*Plus?

New Features in SQL*Plus and iSQL*Pl us 10.1

SQL*Plus Quick Start

Resources

What is SQL*Plus

Before Starting SQL*Plus or iSQL*Plus

Starting SQL*Plus Comm and-line

Starting SQL*Plus Windows GUI

Starting and Stopping the iSQL*Plus Application Server

Starting iSQL*Plus

Connecting to a Different Database

Sample Schemas and SQL*Plus

Running your first Query

Exiting SQL* Plus

Part I SQL*Plus Getting Started

1 SQL*Plus Overview

What is SQL*Plus

SQL*Plus Command-line and Win dows GUI Architecture

SQL*Plus Client

Oracl e Database

iSQL*Plus Architecture

Web Browser

Application Server

Oracle Datab ase

SQL*Plus Installation

SQL*Plu s Date Format

Who Can Use SQL*Plus

How Can I Learn SQL*Plus

How to Use the SQL*Plus Guide

Oracle Database Sample Schemas and SQL*Plus

Unlocking the Sample Tables

2 SQL*Plus User Interface

SQL*Plus Command-line User Interface

The Command-line Screen

Changing the Command-line Font and Font Size

< dl>

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 Window s GUI Font and Font Size

Using a Special Character

iSQL*Plus User Interface

iSQL*Plus Navigatio n

Icons

Tabs

Menus

Footer Links

iSQL*Plus Login Screen

Username:

Password :

Connection Identifier:

Login

< dd>iSQL*Plus DBA Login Screen

User Name

Password

Username:

Password:

Connection Identifier:

Privilege:

Login

iSQL*Plus Workspace

Clear

Execute

Load Script

Save Script

Cancel

Next Page

iSQL*Plus DBA Workspace

iSQL*Plus History Screen

Script

Load

Delete

< em>iSQL*Plus Input Required Screen

Enter Value for sortcol

Continue

Cancel

iSQL*Plus Preferences Screen

Cancel

Apply

Interface Configuration

System Configuratio n

Script Formatting

Script Execution

< a href="ch2.htm#sthref344">Database Administration

Change Password

Preferences and Equivalent SET Commands

3 Configuring SQL*Plus

SQL*Plus and iSQL*Plus Environment Variables

SQL*Plus and iSQL*Plus Conf iguration

Site Profile

Default Site Profile Script

User Profile

Modifying Your LOGIN File

Storing and Restoring SQL*Plus System Variables

Restoring the System Variables

Running the hlpb ld.sql Script to Install Command-line Help

Running the helpdrop.sql Script to Remove Command -line Help

Configuring Oracle Net Services

iSQL*Plus Application Server Configuration

Changing the i SQL*Plus Application Server Port in Use

Testing if the iSQL*Plus Application S erver is Running

Setting the Level of iSQL*Plus Logging

Setting the Session Time Out

Enabling or Disabling Restricted Database Access< /a>

Enabling iSQL*Plus DBA Access

Cr eate Users

List Users

Grant Users the webDba Role

Remove Users

Revoke the webDba Role

Change User Passwords

Test iSQL*Plus DBA Access

Enabling SSL with iSQL*Plus

1. G enerate Keys and Storage File

2. Load Root Certificate into Storage File

3. Request Certificate from CA

4. Import Certificate into Storage File

5. Configure iSQL*Plus to run in SSL mode

Enabling or Disabling iSQL*Plus or iSQL*Plus Help

Enabling User Defined HTML Markup

iSQL*Plus 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 SQL*Plus on Windows

Using the Registry

SQLPATH Registry Entry

SQLPLUS_FONT Registry Entry

SQLPLUS_FONT_SIZE Registry Entry

4 Starting SQL*Plus

Login Username and Password

Changing your Password

Changing Your Password in iSQL*Plus

Username:

Old password:

New password:

Retype new passwo rd:

Apply

Cancel

Expired Password

Expired Password Screen in iSQL*Plus

Connecting to a Database

Net Service Name

Full Connection Identifier

Easy Connection Identifie r

Connectionless Session with /NOLOG

Star ting SQL*Plus

Starting Command-line SQL*Plus

Shortcuts to Starting Command-line SQL*Plus

Getting Command-line Hel p

Starting the Windows Graphical User Interface

Starting the GUI from the Windows Menu

Starting the GUI from the Windows Command Prom pt

Starting the iSQL*Plus Application Server

To Check the HTTP Port used by the iSQL*Plus Application Server

Stoppi ng the iSQL*Plus Application Server

Running iSQL*Plus

Running iSQL*Plus as a DBA

Starting iSQL*Plus from a URL

Examples

Getting Help in iSQL*Plus

Exiting SQL*Plus

Exiting the Command-line User Interface

Exiting the Windows Graphical User Interface< /dd>

Exiting the iSQL*Plus 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 SQL*Plus

5 SQL*Plus 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

Div iding a SQL Command into Separate Lines

Ending a SQL Command

Running PL/SQL Blocks

Creating Stored Procedures

Running SQL*Plus Commands

Understa nding SQL*Plus Command Syntax

Continuing a Long SQL*Plus Command on Additional Lin es

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 A utomatically

Interpreting Error Messages

6 Using Scripts in SQL*Plus

Editing Scripts

Writing Scripts with a System Editor

Editing Scripts in SQL*Plus Command-Line

Listing the Buffer Contents

Editing the Current Line

Appen ding Text to a Line

Adding a New Line

Deleting Line s

Placing Comments in Scripts

U sing the REMARK Command

Using /*...*/

Using - -

Notes on Placing Comments

Running Scripts

Running a Script as You Start SQL*Plus

Nesting Scripts

Exiting from a Script with a Return Code

Defining Substitution Variables

Using Predefined Variables

Using Substitution Variables

Where and How to Use Substitut ion Variables

Avoiding Unnecessary Prompts for Values

Restrictions

System Variables and iSQL*Plus Preferences

Substitution Variables in iSQL*Plus

i SQL*Plus 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 Inp ut

Clearing the Screen

Using Bind Varia bles

Creating Bind Variables

Referencin g Bind Variables

Displaying Bind Variables

Using REFCURSOR Bind Variables

7 Formatting SQL*Plus Reports

Formatting Columns

Changing Column Headings

Default Headings< /a>

Changing Default Headings

Formatting NU MBER Columns

Default Display

Changing t he Default Display

Formatting Datatypes

Default Display

Changing the Default Display

Copying Column Display Attributes

Listing and Resetting Column Display Att ributes

Suppressing and Restoring Column Display Attributes

Printing a Line of Characters after Wrapped Column Values

Clarifying Y our Report with Spacing and Summary Lines

Suppressing Duplicate Values in Break C olumns

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 w hen 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

< a href="ch7.htm#sthref1349">Entering Long Titles

Displaying System-Maintained Val ues in Titles

Listing, Suppressing, and Restoring Page Title Definitions

Displaying Column Values in Titles

Displaying the Current Date in T itles

Setting Page Dimensions

Storing a nd Printing Query Results

Creating a Flat File

Sending Results to a File

Sending Results to a Printer

8 Generating HTML Reports from SQL *Plus

Creating Reports using Command-line SQL*Plus

Creating Reports

Suppressing the Display of SQL*Plus Commands in Re ports

HTML Entities

Creating Reports us ing iSQL*Plus

9< /span> Tuning SQL*Plus

Tracing Statements

Controlling the Autotrace Report

Execution Plan

Statistics

Collecting Timing Statistics

Tracing Parallel and Distributed Queries

SQL*Plus 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 TE RMOUT

SET TRIMOUT ON
SET TRIMSPOOL ON

UNDEF INE

10 SQL*Plus Security

PRODUCT_USER_PROFILE Table

Creating the PUP Table

PUP Table Structure

Description and Use of PUP Columns

PUP Table Administration

< /dd>

Disabling SQL*Plus, SQL, and PL/SQL Commands

Creati ng and Controlling Roles

Disabling SET ROLE

Disabling User Roles

Disabling Commands with SQLPLUS -RESTRICT

< dd>Program Argument Security

iSQL*Plus Security

Enabling SSL with iSQL*Plus

Adm inistration Privileges

Enabling DBA Access

Enab ling or Disabling Restricted Database Access

Security Usage Notes

11 Database Administration with SQL*Plus

Overview

Introduction to Database Startup and Shutdown

Database Startup

Database Shutdown

Redo Log Files

ARCHIVELOG Mode

Database Recovery

12 SQL*Plus Globalization Support< /h3>

Configuring Globalization Support in Command-line SQL*Plus

SQL*Plus Client

Oracle Database

Configuring Multiple Language Support in iSQL*Plus

We b Browser

Application Server

NLS_LANG Environment Variable

Viewing NLS_LANG Settings

Setting NLS_LANG

Part I II SQL*Plus Reference

13 SQL*Plus Command Reference

@ ("at" sign)

@@ (double "at" sign)

/ (slash)

ACC EPT

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

S ET 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]

< dd>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 COM[PATIBILITY]{V7 | V8 | NATIVE}

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 iSQL*Pl us

SET LOBOF[FSET] {1 | n}

SET LOGSOURCE [pathname]

SET LONG {80 | n}

SET LONGC[HUNKSIZE] {80 | n}

SET MARK[UP] HTML [ON | O FF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FOR MAT] {ON | OFF}]

SET NEWP[AGE] {1 | n | NONE}

SET NULL text

SET NUMF[ORMAT] format

SET NUM[WIDTH] {10 | n}

SET PAGES[IZE] {1 4 | n}

SET PAU[SE] {ON | OFF | text}

SET RECSEP {WR[APPED] | EA[CH] | OFF}

SET RECSEPCHAR { | c}

SET SERVEROUT[PUT] {ON | OFF} [SIZE n] [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< /u> | OFF}

SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

< dl>

SQL*Plus Compatibility Matrix

SET SQLPRE[F IX] {# | 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}< /dd>

SET WRA[P] {ON | OFF}

SHOW

SHUTDOWN

SPOOL

ST ART

STARTUP

STORE

TIMING

TTITLE

UNDEFINE

VARIABLE

WHENEVER OSERROR

WH ENEVER SQLERROR

14 SQL*Plus Error Messages

SQL*Plus Error Messages

iSQL*Plus Error Messages

COPY Command Messages

Part IV SQL*Plus Appendixes

A SQL*Plus Limits

B SQL*Plus 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 th e Messages that COPY Displays

Specifying Another User's Table

Copying Data between Tables on One Database

C Obsolete SQL*Plus Commands

SQ L*Plus Obsolete Command Alternatives

BTI[TLE] text (obsolete old form)

COL[UMN] {column|expr} DEF[AULT] (obsolete)

DOC[UMENT] (obsolete)

SET BUF[FER] {buffer|SQL} (obsolete)

SET CLOSECUR[SOR] {ON|OFF} (obsolete)

SET DOC[UMENT] {ON|OFF} (obsolet e)

SET MAXD[ATA] n (obsolete)

SET SCAN { ON|OFF} (obsolete)

SET SPACE {1|n} (obsolete)

SET TRU[NCATE] {ON|OFF} (obsolete)

SHO[W] LABEL (obsolete)

TTI[TLE] text (obsolete old form)

D Commands Not Supported in iSQL*Plus

Index