每次打开Sql Plus都要设置环境变量，很麻烦，这里有个方法可以永久的保存你的环境变量的设置。
设置$ORACLE_HOME/sqlplus/admin/glogin.sql文件，在后边加上你自己的个性化设置。这样每次启动Sqlplus(不管是官方的sqlplus客户端，还是cmd命令行启动的sqlplus)，都会读取glogin.sql的设置。
下边是我glogin.sql里的设置:
-------------------------------------------prompt Loading PTIAN glogin.sql file...--提示，在加载自定义的配置set linesize 999--如果字段都比较多，linesize要尽可能的大set pagesize 30--设置每页的行数，将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting information--Sets the heigth of a page to n. If n=0, the page's heigth is considered infinite. set wrap off--取消折行，否则输出结果可能会很难看set serveroutput on--By default, SQL*PLUS doesn't read what a PL/SQL programm has written with dbms_output. With set serveroutput on, this behaviour is changed. set echo off-- ECHO = ON will Display the command on screen (+ spool)-- ECHO = OFF will Display the command on screen but not in spool files.-- Interactive commands are always echoed to screen/spool.set heading off--是否显示查询结果的列名，如果设置为 off，将用空白行代替，如果要去除该空白行，可以用 set pagesize 0set trimout on--是否去掉屏幕上输出结果行末尾的空白字符set trimspool on--在 spool 到文件时是否去除输出结果中行末尾的空白字符，之前的隔行可以用该参数去掉，和该参数对应的是 trimout，后者用于屏幕输出set timing on--显示每个sql命令所花的时间set verify on --当使用&变量时，提示变量修改之前和之后的值set AUTOTRACE off--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-------------------------------------------
---------------------------------------------------------------------------------------------------------------------
SQL*PLUS - SET Statement
Syntax:
SET option value SHO[W] optionOptions: most of the options listed below have an abbreviated and a long form e.g. APPINFO or APPI will do the same thingAPPI[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 blockAUTOP[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 STATISTICSBLO[CKTERMINATOR] {.|c|OFF|ON} Set the non-alphanumeric character used to end PL/SQL blocks to cCMDS[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 7CON[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 DB2DEF[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 executedEMB[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) will turn the display off ON will set n=1FLAGGER {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 headingsHEADS[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 commandINSTANCE [instance_path|LOCAL] Change the default instance for your session, this command may only be issued when not already connected and requires Net8LIN[ESIZE] {150|n} Width of a line (before wrapping to the next line) Earlier versions default to 80, Oracle 9 is 150LOBOF[FSET] {n|1} Starting position from which CLOB and NCLOB data is retrieved and displayedLOGSOURCE [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} 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, titlesPAU[SE] {OFF|ON|text} press [Return] after each page enclose 'text' in single quotesRECSEP {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 rowRECSEPCHAR {_|c} Define the RECSEPCHAR character, default= ' 'SCAN {OFF|ON} OFF = disable substitution variables and parametersSERVEROUT[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) bytesSHOW[MODE] {OFF|ON} Display old and new settings of a system variableSPA[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 scriptsTAB {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 statisticsTRIM[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 Gray on this page are deprecated from Oracle 9 onwards - also note that several of the options above have 'gone missing' from the official documentation set - HELP SET is a more accurate reference.
Get a list of these SET options in sql*plus with the command:
SQLPLUS> HELP SET
Example
A demo SQL script with the most common SET options