SQL>set colsep' '; //-域输出分隔符
SQL>set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
SQL> set echo on //设置运行命令是是否显示语句
SQL> set feedback on; //设置显示“已选择XX行”
SQL>set feedback off; //回显本次sql命令处理的记录条数，缺省为on
SQL>set heading off; //输出域标题，缺省为on
SQL>set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。
SQL>set linesize 80; //输出一行字符个数，缺省为80
SQL>set numwidth 12; //输出number类型域长度，缺省为10
SQL>set termout off; //显示脚本中的命令的执行结果，缺省为on
SQL>set trimout on; //去除标准输出每行的拖尾空格，缺省为off
SQL>set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
SQL>set serveroutput on; //设置允许显示输出类似dbms_output
SQL> set timing on; //设置显示“已用时间：XXXX”
SQL> set autotrace on-; //设置允许对执行的sql进行分析
SQL> set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.
以下为英文的，很全。
Set sqlplus system settings and defaults.
Syntax:
SET option value
SHO[W] option options: most of these have an abbreviated and a long form
e.g. APPINFO or APPI will do the same thing You can get a list of the set options in sqlplus with the command
HELP SET
APPI[NFO]{ON|OFF|text}
Application info for performance monitor (see DBMS_APPLICATION_INFO)
ARRAY[SIZE] {15|n}
Fetch size (1 to 5000) the number of rows that will be retrieved in one go.
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}
Autocommit commits after each SQL command or PL/SQL block
AUTOP[RINT] {OFF|ON}
Automatic PRINTing of bind variables.(see PRINT)
AUTORECOVERY [ON|OFF]
Configure the RECOVER command to automatically apply
archived redo log files during recovery - without any user confirmation.
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Display a trace report for SELECT, INSERT, UPDATE or DELETE statements
EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.
STATISTICS displays SQL statement statistics.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS
BLO[CKTERMINATOR] {.|c|OFF|ON}
Set the non-alphanumeric character used to end PL/SQL blocks to c
CMDS[EP] {;|c|OFF|ON}
Change or enable command separator - default is a semicolon (;)
COLSEP { |text}
The text to be printed between SELECTed columns normally a space.
COM[PATIBILITY] {V5|V6|V7|V8|NATIVE}
Version of oracle - see also init.ora COMPATIBILITY=
You can set this back by up to 2 major versions e.g. Ora 9 supports 8 and 7
CON[CAT] {.|c|OFF|ON}
termination character for substitution variable reference
default is a period.
COPYC[OMMIT] {0|n}
The COPY command will fetch n batches of data between commits.
(n= 0 to 5000) the size of each fetch=ARRAYSIZE.
If COPYCOMMIT = 0, COPY will commit just once - at the end.
COPYTYPECHECK {OFF|ON}
Suppres the comparison of datatypes while inserting or appending to DB2
DEF[INE] {&|c|OFF|ON}
c = the char used to prefix substitution variables.
ON or OFF controls whether to replace substitution variables with their values.
(this overrides SET SCAN)
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
Sets the depth of the level to which you can recursively describe an object
(1 to 50) see the DESCRIBE command
ECHO {OFF|ON}
Display commands as they are executed
EMB[EDDED] {OFF|ON}
OFF = report printing will start at the top of a new page.
ON = report printing may begin anywhere on a page.
ESC[APE] {\|c|OFF|ON}
Defines the escape character. OFF undefines. ON enables.
FEED[BACK] {6|n|OFF|ON}
Display the number of records returned (when rows > n )
OFF (or n=0) turns the display off
ON sets n=1
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
non-standard constructs are flagged as errors and displayed
See also ALTER SESSION SET FLAGGER.
FLU[SH] {OFF|ON}
Buffer display output (OS)
(no longer used in Oracle 9)
HEA[DING] {OFF|ON}
print column headings
HEADS[EP] {||c|OFF|ON}
Define the heading separator character (used to divide a column heading onto > one line.)
OFF will actually print the heading separator char
see also: COLUMN command
INSTANCE [instance_path|LOCAL]
Change the default instance for your session, this command may only be issued when
not already connected and requires Net8
LIN[ESIZE] {150|n}
Width of a line (before wrapping to the next line)
Earlier versions default to 80, Oracle 9 is 150
LOBOF[FSET] {n|1}
Starting position from which CLOB and NCLOB data is retrieved and displayed
LOGSOURCE [pathname]
Change the location from which archive logs are retrieved during recovery
normally taken from LOG_ARCHIVE_DEST
LONG {80|n}
Set the maximum width (in chars) for displaying and copying LONG values.
LONGC[HUNKSIZE] {80|n}
Set the fetch size (in chars) for retrieving LONG values.
MARK[UP] HTML [ON|OFF]
[HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}][SPOOL {ON|OFF}]
[PRE[FORMAT] {ON|OFF}]
Output HTML text, which is the output used by iSQL*Plus.
NEWP[AGE] {1|n} NULL text
The number of blank lines between the top of each page and the top title.
0 = a formfeed between pages.
NULL text
Replace a null value with 'text'
The NULL clause of the COLUMN command will override this for a given column.
NUMF[ORMAT] format
The default number format.
see COLUMN FORMAT.
NUM[WIDTH] {10|n}
The default width for displaying numbers.
PAGES[IZE] {14|n}
The height of the page - number of lines.
0 will suppress all headings, page breaks, titles
PAU[SE] {OFF|ON|text}
press [Return] after each page
enclose text in single quotes
RECSEP {WR[APPED]|EA[CH]|OFF}
Print a single line of the RECSEPCHAR between each record.
WRAPPED = print only for wrapped lines
EACH=print for every row
RECSEPCHAR {_|c}
Define the RECSEPCHAR character, default= ' ' SCAN {OFF|ON} OFF = disable substitution variables and parameters
SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
whether to display the output of stored procedures (or PL/SQL blocks)
i.e., DBMS_OUTPUT.PUT_LINE SIZE = buffer size (2000-1,000,000) bytes
SHOW[MODE] {OFF|ON}
Display old and new settings of a system variable SPA[CE] {1|n}
The number of spaces between columns in output (1-10) SQLBL[ANKLINES] {ON|OFF}
Allow blank lines within an SQL command. reverts to OFF after the curent command/block.
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
Convert the case of SQL commands and PL/SQL blocks
(but not the SQL buffer itself)
SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Set the behavior or output format of VARIABLE to that of the
release or version specified by x.y[.z].
SQLCO[NTINUE] {> |text}
Continuation prompt (used when a command is continued on an additional line using a hyphen -)
SQLN[UMBER] {OFF|ON}
Set the prompt for the second and subsequent lines of a command or PL/SQL block.
ON = set the SQL prompt = the line number.
OFF = set the SQL prompt = SQLPROMPT.
SQLPRE[FIX] {#|c}
set a non-alphanumeric prefix char for immediately executing one line of SQL (#)
SQLP[ROMPT] {SQL>|text}
Set the command prompt.
SQLT[ERMINATOR] {;|c|OFF|ON}|
Set the char used to end and execute SQL commands to c.
OFF disables the command terminator - use an empty line instead.
ON resets the terminator to the default semicolon (;).
SUF[FIX] {SQL|text}
Default file extension for SQL scripts
TAB {OFF|ON}
Format white space in terminal output.
OFF = use spaces to format white space.
ON = use the TAB char.
Note this does not apply to spooled output files.
The default is system-dependent. Enter SHOW TAB to see the default value.
TERM[OUT] {OFF|ON}
OFF suppresses the display of output from a command file
ON displays the output.
TERMOUT OFF does not affect the output from commands entered interactively.
TI[ME] {OFF|ON}
Display the time at the command prompt.
TIMI[NG] {OFF|ON}
ON = display timing statistics for each SQL command or PL/SQL block run.
OFF = suppress timing statistics
TRIM[OUT] {OFF|ON}
Display trailing blanks at the end of each line.
ON = remove blanks, improving performance
OFF = display blanks.
This does not affect spooled output.
SQL*Plus ignores TRIMOUT ON unless you set TAB ON.
TRIMS[POOL] {ON|OFF}
Allows trailing blanks at the end of each spooled line.
This does not affect terminal output.
UND[ERLINE] {-|c|ON|OFF}
Set the char used to underline column headings to c.
VER[IFY] {OFF|ON}
ON = list the text of a command before and after replacing substitution variables with values.
OFF = dont display the command.
WRA[P] {OFF|ON}
Controls whether to truncate or wrap the display of long lines.
OFF = truncate
ON = wrap to the next line
The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.
The items in Bold on this page are deprecated or
missing in Oracle 9 - also note that many of the above options have 'gone missing'
from the official documentation set - HELP SET is a more accurate reference1. 设置当前session是否对修改的数据进行自动提交 :SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
SQL> set autocommit off
2．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 :SET ECHO {ON|OFF}
SQL> set echo on
3.是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
SQL> set feed off 对比 SQL> set feed on SQL> select * from dual; SQL> select * from dual; D D - - X X 已选择 1 行(区别于此)
4.是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
SQL> set hea on 对比 SQL> set heading off SQL> select * from a; SQL> select * from a; ID SNAME AGE(列名) 1 不开机 900 ---------- ------------------------ ---------- 1 不开机 900
5.设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示
SQL> set lin 20 SQL> select * from a ; 1 不开机 900
6.设置页与页之间的分隔
当set newpage 0 时，会在每页的开头有一个小的黑方框
SQL> set newpage 0
SQL> select * from a;
1 不开机 900
7.设置一页有多少行数 :SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
SQL> set pagesize 2 --设置每页2行 SQL> set newpage 0 --设置页分隔
SQL> select * from a;
1 不开机 900
1 短路 200
8 10分钟 NULL
8.是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。 :SET SERVEROUT[PUT] {ON|OFF}
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('output sucess'); 3 end; 4 / output sucess (显示输出,默认不显示)
9.显示每个sql语句花费的执行时间 :set TIMING {ON|OFF}
SQL> set timing on SQL> select * from a; 1 不开机 900 已选择1行. 已用时间: 00: 00: 00.00
10.关于SPOOL(SPOOL是SQLPLUS的命令，不是SQL语法里面的东西)，对于SPOOL数据的SQL，最好要自己定义格式，以方便程序直接导入,SQL语句如：
select empno||','||ename||','||sal from emp;
spool常用的设置
set colsep' '; //域输出分隔符 set echo off; //显示start启动的脚本中的每个sql命令，缺省为on set feedback off; //回显本次sql命令处理的记录条数，缺省为on set heading off; //输出域标题，缺省为on set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0 set termout off; //显示脚本中的命令的执行结果，缺省为on set trimout on; //去除标准输出每行的拖尾空格，缺省为off set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
11.set long
是不是可以用set long 命令来增加下面的Text列的宽度？
SQL> select * from user_views; VIEW_NAME TEXT_LENGTH TEXT ------------------------------ ----------- --------------------------------------------------------- V_EMP 76 select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM
是的 , set long指定的是当字段为long类型的时候,显示的长度