SQL*Plus User’s Guide and Reference

Description

Contents Preface Audience Documentation Accessibility Related Documents Conventions What’s New in SQL*Plus? New Features in SQL*Plus Release 10.2 SQL*Plus Quick Start SQL*Plus Resources SQL*Plus Overview SQL*Plus Prerequisites Starting SQL*Plus Command-line Starting SQL*Plus Windows GUI Starting iSQL*Plus Starting SQL*Plus Instant Client 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 User Interface SQL*Plus 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 iSQL*Plus User Interface iSQL*Plus Navigation Icons Tabs Menus Footer Links iSQL*Plus Login Screen Username: Password: Connection Identifier: Login 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 iSQL*Plus Input Required Screen Enter Value for sortcol Continue Cancel iSQL*Plus Preferences Screen Cancel Apply Interface Configuration System Configuration Script Formatting Script Execution Database Administration Change Password Preferences and Equivalent SET Commands 2 Configuring SQL*Plus SQL*Plus and iSQL*Plus Environment Variables SQL*Plus and iSQL*Plus Configuration Site Profile Default Site Profile Script User Profile Modifying Your LOGIN File Storing and Restoring SQL*Plus 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 iSQL*Plus Application Server Configuration Changing the iSQL*Plus Application Server Port in Use Testing if the iSQL*Plus Application Server is Running Setting the Level of iSQL*Plus Logging Setting the Session Time Out Enabling Restricted Database Access Enabling iSQL*Plus DBA Access Create 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. 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 iSQL*Plus to run in SSL mode Enabling iSQL*Plus or iSQL*Plus Help Enabling Unauthorised Access Banner Enabling URL Access 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 3 Starting SQL*Plus Login Username and Password Secure External Password Store Expired Password Expired Password Screen in iSQL*Plus Changing your Password Changing Your Password in iSQL*Plus 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 SQL*Plus Starting Command-line SQL*Plus 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 iSQL*Plus Application Server To Check the HTTP Port used by the iSQL*Plus Application Server Stopping the iSQL*Plus Application Server Starting iSQL*Plus Starting 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 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 4 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 Dividing a SQL Command into Separate Lines Ending a SQL Command Running PL/SQL Blocks Creating Stored Procedures Running SQL*Plus Commands Understanding SQL*Plus Command Syntax Continuing a Long SQL*Plus 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 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 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 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 Substitution Variables Avoiding Unnecessary Prompts for Values Restrictions System Variables and iSQL*Plus Preferences Substitution Variables in iSQL*Plus iSQL*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 Input Clearing the Screen Using Bind Variables Creating Bind Variables Referencing Bind Variables Displaying Bind Variables Using REFCURSOR Bind Variables 6 Formatting SQL*Plus 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 SQL*Plus Creating Reports using Command-line SQL*Plus Creating Reports Suppressing the Display of SQL*Plus Commands in Reports HTML Entities Creating Reports using iSQL*Plus 8 Tuning SQL*Plus Tracing Statements Controlling the Autotrace Report Execution Plan Statistics Collecting Timing Statistics Tracing Parallel and Distributed Queries Execution Plan Output in Earlier Databases 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 TERMOUT SET TRIMOUT ON SET TRIMSPOOL ON UNDEFINE 9 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 Program Argument Security iSQL*Plus Security Enabling SSL with iSQL*Plus Administration Privileges Enabling DBA Access Enabling or Disabling Restricted Database Access Security Usage Notes 10 Database Administration with SQL*Plus Overview Introduction to Database Startup and Shutdown Database Startup Database Shutdown Redo Log Files ARCHIVELOG Mode Database Recovery 11 SQL*Plus Globalization Support Configuring Globalization Support in Command-line SQL*Plus SQL*Plus Client Oracle Database Configuring Multiple Language Support in iSQL*Plus Web Browser Application Server NLS_LANG Environment Variable Viewing NLS_LANG Settings Setting NLS_LANG Part III SQL*Plus Reference 12 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 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 iSQL*Plus 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]} SQL*Plus 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 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 the Messages that COPY Displays Specifying Another User's Table Copying Data between Tables on One Database C Obsolete SQL*Plus Commands SQL*Plus 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 iSQL*Plus E SQL*Plus Instant Client Choosing the SQL*Plus Instant Client to Install Basic Instant Client Lightweight Instant Client Lightweight SQL*Plus Instant Client Error with Unsupported Character Set Installing SQL*Plus Instant Client by Downloading from OTN Installing SQL*Plus Instant Client from Linux RPM Packages Installing SQL*Plus Instant Client from the UNIX or Windows Zip Files List of Files Required for SQL*Plus Instant Client Installing SQL*Plus Instant Client from the 10g Client Release Media Installing SQL*Plus Instant Client on UNIX or Linux Installing SQL*Plus Instant Client on Windows Configuring SQL*Plus Instant Client Configuring SQL*Plus Instant Client on Linux (from RPMs) Configuring SQL*Plus Instant Client on Linux (from Client Media or Zip File) and UNIX Configuring SQL*Plus Instant Client on Windows Connecting to a Database with SQL*Plus Instant Client AS SYSDBA or AS SYSOPER Connections with SQL*Plus Instant Client Uninstalling Instant Client Uninstalling SQL*Plus 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