Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Documentation Structure

Related Documentation

Conventions

What's New in SQL*Plus?

New Features in SQL*Plus 9.2

New Features in iSQL*Plus 9.2

Upgrading iSQL*Plus Server from 9.0.1 to 9.2

New Features in SQL*Plus 9.0.1

SQL*Plus Quick Start

iSQL*Plus Quick Start

SQL*Plus Command-line Quick Start for Windows

SQL*Plus Command-line Quick Start for UNIX

Part I SQL*Plus Getting Started

1 SQL*Plus Overview

What is SQL*Plus

SQL*Plus Command-line Architecture

iSQL*Plus Architecture

SQL*Plus Installation

Who Can Use SQL*Plus

How Can I Learn SQL*Plus

How to Use the SQL*Plus Guide

Oracle9i Sample Tables and SQL*Plus

Unlocking the Sample Tables

2 SQL*Plus User Interface

SQL*Plus Command-line User Interface

The Command-line Screen

iSQL*Plus User Interface

iSQL*Plus Navigation

iSQL*Plus Login Screen

iSQL*Plus Work Screen

iSQL*Plus History Screen

iSQL*Plus Preferences Screen

iSQL*Plus User Interface Options

iSQL*Plus DBA Login Screen

iSQL*Plus DBA Work Screen

iSQL*Plus Server Statistics

Setting System Variables in iSQL*Plus

iSQL*Plus Extension for Windows

3 Configuring SQL*Plus

SQL*Plus Configuration

Site Profile

User Profile

Storing and Restoring SQL*Plus System Variables

iSQL*Plus Configuration

Configuring the Client Tier

Configuring the Middle Tier: iSQL*Plus Server

Configuring the Middle Tier: Oracle HTTP Server

Configuring the Middle Tier: Oracle Net

Configuring the Database Tier

iSQL*Plus Extension for Windows Configuration

Configuration Dialog

4 Starting SQL*Plus

Starting Command-line SQL*Plus

The SQLPLUS Command

Options

Logon

Start

Getting Command-line Help

Installing Command-line Help

Exiting Command-line SQL*Plus

Starting iSQL*Plus

Getting Help in iSQL*Plus

Exiting iSQL*Plus

Starting iSQL*Plus from a URL

Login Username and Password

Changing your Password

Changing Your Password in iSQL*Plus

Multi-User Systems

Single-User Systems

Database Connection Identifier

Connecting to a Remote Database as You Start SQL*Plus

Connecting to a Remote Database Using the CONNECT Command

Part II Using SQL*Plus

5 SQL*Plus Basics

Entering and Executing Commands

The SQL Buffer

Executing Commands

Running SQL Commands

Running PL/SQL Blocks

Running SQL*Plus Commands

Understanding SQL*Plus Command Syntax

System Variables that Affect How Commands Run

Saving Changes to the Database Automatically

Stopping a Command while it is Running

Running Host Operating System Commands

Getting Help

Listing a Table Definition

Listing PL/SQL Definitions

Controlling the Display

Interpreting Error Messages

6 Using Scripts in SQL*Plus

Editing Scripts

Writing Scripts with a System Editor

Modifying Scripts

Editing Scripts in SQL*Plus

Placing Comments in Scripts

Using the REMARK Command

Using /*...*/

Using - -

Notes on Placing Comments

Running Scripts

Nesting Scripts

Receiving a Return Code

Exiting from a Script with a Return Code

Writing Interactive Commands

Defining User Variables

Using Substitution Variables

Substitution Variables in iSQL*Plus

Passing Parameters through the START Command

Communicating with the User

Using Bind Variables

Creating Bind Variables

Referencing Bind Variables

Displaying Bind Variables

Using REFCURSOR Bind Variables

7 Formatting SQL*Plus Reports

Formatting Columns

Changing Column Headings

Formatting NUMBER Columns

Formatting Datatypes

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

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

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

Creating Reports with CGI Scripts

Suppressing the Display of SQL*Plus Commands in Reports

HTML Entities

Creating Reports using iSQL*Plus

9 Tuning SQL*Plus

Tracing Statements

Controlling the Report

Execution Plan

Statistics

Collecting Timing Statistics

Tracing Parallel and Distributed Queries

System Variables Influencing SQL*Plus Performance

iSQL*Plus Server Statistics

Interpreting Active Statistics

iSQL*Plus Tuning Notes

10 SQL*Plus Security

PRODUCT_USER_PROFILE Table

Creating the PUP Table

PUP Table Structure

Description and Use of PUP Columns

PUP Table Administration

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

Creating and Controlling Roles

Disabling SET ROLE

Disabling User Roles

Disabling Commands with SQLPLUS -RESTRICT

iSQL*Plus Security

Administration Privileges

Enabling Server Authentication for Users

Enabling or Disabling Restricted Database Access

Enabling DBA Access

Adding Entries to an Oracle HTTP Server Authentication File

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

Configuring Globalization Support in Command-line SQL*Plus

Configuring Globalization Support in iSQL*Plus

Part III SQL*Plus Reference

13 SQL*Plus Command Reference

SQL*Plus Command Summary

@ ("at" sign)

@@ (double "at" sign)

/ (slash)

ACCEPT

APPEND

ARCHIVE LOG

ATTRIBUTE

BREAK

BTITLE

CHANGE

CLEAR

COLUMN

COMPUTE

CONNECT

COPY

DEFINE

DEL

DESCRIBE

DISCONNECT

EDIT

EXECUTE

EXIT

GET

HELP

HOST

INPUT

LIST

PASSWORD

PAUSE

PRINT

PROMPT

RECOVER

REMARK

REPFOOTER

REPHEADER

RUN

SAVE

SET

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 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 LOBOF[FSET] {n|1}

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] {24|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|OFF}

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

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}

SHOW

SHUTDOWN

SPOOL

START

STARTUP

STORE

TIMING

TTITLE

UNDEFINE

VARIABLE

WHENEVER OSERROR

WHENEVER 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

Copying Data from One Database to Another

Copying Data between Tables on One Database

C Obsolete SQL*Plus Commands

D Commands Not Supported in iSQL*Plus

Glossary

Index