ORACLE SQL*PLUS环境变量设置及说明
1:查看当前用户的环境设置:
SQL> define DEFINE _DATE = "21-JAN-14" (CHAR) DEFINE _CONNECT_IDENTIFIER = "updb" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR) DEFINE _EDITOR = "ed" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000300" (CHAR) SQL>
2:当前用户设置环境:
SQL> define_editor = 'VI'; SQL> define DEFINE _DATE = "21-JAN-14" (CHAR) DEFINE _CONNECT_IDENTIFIER = "updb" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR) DEFINE _EDITOR = "VI" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000300" (CHAR) SQL>
3:编辑login.sql
当退出当前用户的时候,所设置的环境就自动恢复成默认值,可以在login.sql文件中做设置,就不用每次都手动修改:
[oracle@steven ~]$ cat login.sql 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
4:对login.sql脚本进行说明
define_editor=vi:设置SQL*Plus 使用的默认编辑器。可以把它设置为你中意的文本编辑器。
set serveroutput on size unlimited:默认的打开DBMA_OUTPUT,设置缓冲区的大写尽可能大。
set trimspool on:假脱机输出文件时,会去除问本行两端的空格,而且行宽不定,如果设置为OFF,假脱机输出文件宽度则等于所设置的linesize。
set long 5000:设置选择LONG和CLUB列时显示的默认字节数。
set linesize 100:设置SQL*Plus显示的文本行宽为100个字符。
set pagesize 9999:pagesize可以控制SQL*Plus多久打印一次标题。
column paln_plus_exp format a80:设置由AUTOTRACE得到的解释计划输出的默认宽度。a80通常足以放下整个计划。
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> ':这部分用于简历SQL*Plus的提示符,告诉SQL*Plus取得global_name列中的最后一个值,并将这个值赋给变量gname.