SQL*Plus

Oracle Database Administration

SYS-ED/Computer Education Techniques, Inc. Ch: 2:1

 Objectives
You will learn:
• Purpose and features of SQL*Plus.
• Starting/stopping SQL*Plus.
• Configuring SQL*Plus.
• Executing commands.
• SQL buffer.
• Formatting columns.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:2

 What is SQL*Plus
• SQL*Plus is an interactive and batch query tool that is installed with
Oracle Database Server or Client installation.
• It has a command-line user interface, a Windows Graphical User
Interface (GUI) and the iSQL*Plus web-based user interface.
• SQL*Plus has its own commands and environment, and provides
access to the Oracle Database. It provides the capability for SQL,
PL/SQL, and SQL*Plus commands to be entered and for operating
system commands to perform the following:
– Format, perform calculations on, store, and print from query results.
– Examine table and object definitions.
– Develop and run batch scripts.
– Perform database administration.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:3

 Start SQL*Plus
• Open a UNIX or a Windows terminal and
enter the SQL*Plus command:
sqlplus
• Or enter the SQL*Plus command in the
form:
sqlplus username/password

SYS-ED/Computer Education Techniques, Inc. Ch: 2:4

 Start SQL*Plus
• Open a UNIX or a Windows terminal and
enter the SQL*Plus command:
sqlplus username/password@connect_identifier

• To hide a password, enter the SQL*Plus

command in the form:
sqlplus username@connect_identifier

SYS-ED/Computer Education Techniques, Inc. Ch: 2:5

 iSQL*Plus Session
• Enter the iSQL*Plus URL in the web
browser's location or address field.
• The iSQL*Plus URL looks like:
http://machine_name.domain:port/isqlplus

SYS-ED/Computer Education Techniques, Inc. Ch: 2:6

Connecting to a Different Database
• To connect to a different database from a
current command-line session
connect username/password@connect_identifier

SYS-ED/Computer Education Techniques, Inc. Ch: 2:7

 Unlocking the Sample Tables
• The HR account needs to be unlocked before
the HR sample schema can be used.
• To unlock the HR account, log in as the
SYSTEM user and enter the following command,
where password is the password to be defined
for the user HR:

ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;

SYS-ED/Computer Education Techniques, Inc. Ch: 2:8

 Configuring SQL*Plus
• There are two operating system files to do
this:
– The Site Profile file, glogin.sql, for site wide
settings, and settings for the iSQL*Plus
sessions from an iSQL*Plus Application
Server.
– Additionally, in the command-line user
interface and the Windows GUI, the User
Profile, login.sql, sets user specific settings.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:9

 Site Profile
• A Site Profile script is created during installation.
It is used by the database administrator to
configure session wide behavior for SQL*Plus
Command-line, Windows GUI and iSQL*Plus
connections.
• The Site Profile script is generally named
glogin.sql. SQL*Plus or the iSQL*Plus Server
executes this script whenever a user starts a
SQL*Plus or iSQL*Plus session and successfully
establishes the Oracle Database connection.
SYS-ED/Computer Education Techniques, Inc. Ch: 2:10
 Global Login
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command
--
-- USAGE
-- This script is automatically run
-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command

COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command

COLUMN name_col_plus_show_sga FORMAT a24

SYS-ED/Computer Education Techniques, Inc. Ch: 2:11

 Global Login
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN

COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report

COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

SYS-ED/Computer Education Techniques, Inc. Ch: 2:12

Entering and Executing Commands
• Three kinds of commands can be entered:
– SQL commands, for working with information
in the database.
– PL/SQL blocks, also for working with
information in the database.
– SQL*Plus commands, for formatting query
results, setting options, and editing and
storing SQL commands and PL/SQL blocks.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:13

 The SQL Buffer
• The SQL buffer stores the most recently
entered SQL command or PL/SQL block
(but not SQL*Plus commands).
• The command or block remains in the
buffer until replaced by the next SQL
command or PL/SQL block.
• The buffer contents can be viewed with
the LIST command.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:14

 The SQL Buffer
• The command or block in the SQL buffer
command can be executed using the RUN
or /(slash) commands.
• RUN displays the command or block in the
buffer before executing it.
• /(slash) executes the command or block in
the buffer without displaying it first.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:15

 DESCRIBE Command
• To list the column definitions of the
columns in the sample view
EMP_DETAILS_VIEW, enter:
DESCRIBE EMP_DETAILS_VIEW

• DESCRIBE accesses information in the

Oracle Database data dictionary.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:16

 Ending a SQL Command
• A SQL command can be entered in one of three
ways:
– with a semicolon (;). A semicolon (;) informs
SQL*Plus that the command is to be run.
– with a slash (/) on a line by itself. A slash (/) informs
SQL*Plus that the command is to be run.
– with a blank line. A blank line in a SQL statement or
script informs SQL*Plus that a command has been
finished being entered, but that it has not been run
yet.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:17

 Stopping a Command
while it is Running
• Assume that the first page of a 50 page
report has been displayed and the rest of
the page does not have to be displayed.
• Press Cancel, the system's interrupt
character, which is typically CTRL+C.
• SQL*Plus stops the display. In iSQL*Plus,
click the Cancel button.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:18

 Running Operating
System Commands
• To run an operating system command,
enter the SQL*Plus command HOST
followed by the operating system
command.
• For example, this SQL*Plus command
runs the command, DIRECTORY *.SQL:
HOST DIRECTORY *.SQL

SYS-ED/Computer Education Techniques, Inc. Ch: 2:19

 Saving Changes to
the Database Automatically
• Changes can be specified to make the information stored in the
database using the SQL Database Manipulation Language (DML)
commands UPDATE, INSERT, and DELETE - which can be used
independently or within a PL/SQL block.
• These changes are not made permanent until a SQL COMMIT
command or a SQL Database Control Language (DCL) or Database
Definition Language (DDL) command (such as CREATE TABLE)
has been entered. The autocommit feature can also be used. The
SQL*Plus autocommit feature causes pending changes to be
committed after a specified number of successful SQL DML
transactions.
• To turn the autocommit feature on, enter:
SET AUTOCOMMIT ON

SYS-ED/Computer Education Techniques, Inc. Ch: 2:20

 Line Edit
APPEND text A text Adds text at the end of the current line.
CHANGE/old/new C/old/new Changes old to new in the current line.
CHANGE/text C/text Deletes text from the current line.
CLEAR BUFFER CL BUFF Deletes all lines.
DEL (none) Deletes the current line.
DEL n (none) Deletes line n.
DEL * (none) Deletes the current line.
DEL n * (none) Deletes line n through the current line.
DEL LAST (none) Deletes the last line.
DEL m n (none) Deletes a range of lines (m to n).
DEL * n (none) Deletes the current line through line n.
INPUT I Adds one or more lines.
INPUT text I text Adds a line consisting of text.
LIST ; or L Lists all lines in the SQL buffer.
LIST n L n or n Lists line n.
LIST * L* Lists the current line.
LIST n * Ln* Lists line n through the current line.
LIST LAST L LAST Lists the last line.
LIST m n Lmn Lists a range of lines (m to n).
LIST * n L*n Lists the current line through line n.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:21

 Formatting Columns
• Changing column headings
• Default headings
• Changing default headings
• COLUMN column_name HEADING
column_heading

SYS-ED/Computer Education Techniques, Inc. Ch: 2:22

 Column Heading
• Splitting a column heading.
COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'

SYS-ED/Computer Education Techniques, Inc. Ch: 2:23

 Formatting NUMBER Columns
• When displaying NUMBER columns,
either accept the SQL*Plus default display
width or it can be changed using the
COLUMN command.

COLUMN SALARY FORMAT $99,990

SYS-ED/Computer Education Techniques, Inc. Ch: 2:24

 Starting and Mounting

• To start an Oracle Database instance,

without mounting the database, enter:
STARTUP NOMOUNT
• To start an instance, mount the database,
but leave the database closed, enter:
STARTUP MOUNT

SYS-ED/Computer Education Techniques, Inc. Ch: 2:25

 Opening the Database

• To start an instance using the Oracle Database Server

parameter file INITSALES.ORA, mount and open the
database named SALES, and restrict access to
database administrators, enter:
STARTUP OPEN sales PFILE=INITSALES.ORA RESTRICT
• To start an instance using the Oracle Database Server
parameter file INITSALES.ORA, mount and open the
database named SALES in exclusive mode, and restrict
access to administrative personnel, enter:
STARTUP OPEN sales PFILE=INITSALES.ORA EXCLUSIVE
RESTRICT

SYS-ED/Computer Education Techniques, Inc. Ch: 2:26

 Shutting Down a Database

• Closing the database

– When a database is closed, all database and recovery data in
the SGA are written to the datafiles and redo log files, and all
online datafiles are closed.
• Dismounting the database
– Dismounting the database disassociates the database from an
instance and closes the control files of the database.
• Shutting down the instance
– Shutting down an instance reclaims the SGA from memory and
terminates the background Oracle Database processes that
constitute an Oracle Database instance.

SYS-ED/Computer Education Techniques, Inc. Ch: 2:27

SQL*Plus Command Reference
@ ("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

SYS-ED/Computer Education Techniques, Inc. Ch: 2:28

SQL*Plus Command Reference
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]]

SYS-ED/Computer Education Techniques, Inc. Ch: 2:29

SQL*Plus Command Reference
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*Plus
SET LOBOF[FSET] {1 | n}
SET LOGSOURCE [pathname]
SET LONG {80 | n}
SET LONGC[HUNKSIZE] {80 | n}

SYS-ED/Computer Education Techniques, Inc. Ch: 2:30

SQL*Plus Command Reference
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] [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}

SYS-ED/Computer Education Techniques, Inc. Ch: 2:31

SQL*Plus Command Reference
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

SYS-ED/Computer Education Techniques, Inc. Ch: 2:32