SQL*Plus Reminder

This is a reminder of all those Oracle SQL*Plus commands that you and I should know.
1. Saving SQL to a file:
Save filename[.ext] [cre[ate]]|[replace]|[append]
Default ext is SQL.

2. Running a SQL script without loading first:
Start filename
Or on the command line:
Sqlplus user/password@database @filename

3. Connecting as another user:
Conn[ect] [username][/password/[@database]

4. Disconnecting:
Disconnect

5. Editing a Statement:
Edit Commands:

Append	A	Adds text to the end of the current line
Change /old/new	C	Changes the current line – replaces text
Clear Buffer	CL BUFF	Deletes all lines from the edit buffer
DEL		Deletes the current line
INPUT	I	Adds one or more lines
LIST	L	Lists all lines in the SQL Buffer

6. Repeat last statement:
/

7. Load a file into the buffer ready for editing:
Get filename

8. Save Query results to a file:
Spo[ol] [filename[.ext]|[off]|[on]
Spool off – stop writing to the buffer
Spool on – writes to the buffer
Default extention is .lst
Default location is your current working directory.

9. Display SQL*Plus system variables:
Show all
appinfo is ON and set to "SQL*Plus"
arraysize 15autocommit OFF
autoprint OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
closecursor OFF
cmdsep OFF
colsep " "
compatibility NATIVE
concat "." (hex 2e)
copycommit 0
copytypecheck is ON
crt ""
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 24
long 80
longchunksize 80
maxdata 60000
newpage 1
non-blocking ON
null ""
numformat ""
numwidth 9
pagesize 24
pause is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 703040001
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "SQL"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the 1st few characters of the next SELECT statement
underline "-" (hex 2d)
user is "NOVA"
verify ON
shiftinout INVISIBLE
wrap : lines will be wrapped

To display all system variables, Select the one you want and then change it – for example change the sql prompt to "FRED>"
set sqlprompt "FRED>"

10. Formatting reports – use the system variables above.
For example:
TTITLE CENTER ‘My Report’s top Title’
BTITLE LEFT ‘the Bottom title’
SET LINESIZE 80
SET FEEDBACK OFF
Now run your script.

Other formatting commands

Break on	Controls where spaces are placed between sections and where to break for subtotals and totals
Btitle	Sets the bottom title
Column	Sets the heading and formatting of a column
Compute	Makes SQL*Plus compute a variety of totals
Remark	Words following this command are comments
Save	Saves the contents of the SQL buffer to disk
Set linesize	Sets the width of a line in characters for a report
Set newpage	Sets the number of lines between pages of a report
Spool	Tells SQL*Plus to write to an output file
Start	Tells SQL*Plus to execute a file.
ttitle	Sets the title for a report.

11. Defining user variables is SQL*Plus
DEFINE variablename = string
Ask the user for a value
Accept variablename
Or put an ampersand in front of the variable name
Define MyDate = 01-03-1999
Select &MyDate from tablename

12. Show the progress of and SQL script
Set Echo On

13. Log the progress of an SQL script to a file
SPOOL output.txt

14. Show the entire contents of a column
Set long 2000

15. Showing the users connected to your database
select username, sid, serial# from v$session;

16. Kill a user’s session…
Alter system
Kill session _&sid, &serial

17. Timing SQL commands
set timing on
select whatever..
real: 1234
Show that the select whatever took 1234 milliseconds.
Also use Timing system variable
Timing start Mytimer
Query MyTimer
Timing show mytimer
Stopping Mytimer
Timing stop mytimer.

17. Enabling the Explain Plan
set autotrace on explain