set the SQLPLUS Environment

SQL>SET SERVEROUTPUT ON

define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname


select lower(user) || '@' || substr( global_name, 1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

• DEFINE _EDITOR=VI: This sets up the default editor SQL*Plus will use. You may set the default editor to be your favorite text editor (not a word processor) such as Notepad or emacs.
• SET SERVEROUTPUT ON SIZE 1000000: This enables DBMS_OUTPUT to be on by default (hence you don’t have to type it in each and every time). It also sets the default buffer size as large as possible.
• SET TRIMSPOOL ON: When spooling text, lines will be blank-trimmed and not fixed width. If this is set to OFF (the default), spooled lines will be as wide as your LINESIZE setting.
• SET LONG 5000: This sets the default number of bytes displayed when selecting LONG and CLOB columns.
• SET LINESIZE 100: This sets the width of the lines displayed by SQL*Plus to be 100 characters.
• SET PAGESIZE 9999: This sets the PAGESIZE, which controls how frequently SQL*Plus prints out headings, to a large number (you get one set of headings per page).
• COLUMN PLAN_PLUS_EXP FORMAT A80: This sets the default width of the explain plan output you receive with AUTOTRACE. A80 is generally wide enough to hold the full plan.

define gname=idle

column global_name new_value gname

select lower(user) || '@' || substr( global_name,1,

decode( dot, 0, length(global_name), dot-1) ) global_name

from (select global_name, instr(global_name,'.') dot from global_name );

set sqlprompt '&gname> '

• The directive COLUMN GLOBAL_NAME NEW_VALUE GNAME tells SQL*Plus to take the last value it retrieves for any column named GLOBAL_NAME and place it into the substitution variable GNAME. I then select the GLOBAL_NAME out of the database and concatenate this with the username I am logged in with. That makes my prompt look like this:

ops$tkyte@ora10g>

so I know who I am as well as where I am.