sqlplus环境配置(login.sql)


		sqlplus环境配置(login.sql)
 
在Oracle中，有两份文件可以设置SQL*Plus的使用环境：
 
（1）全局设置文件：$ORACLE_HOME/sqlplus/admin/glogin.sql
 
（2）个性化设置文件：login.sql，然后有操作系统环境变量指定其位置：
 
[plain] 
exportSQLPATH=/home/oracle/oracle_relate  
 
常用的login.sql如下：
[plain] 
define _editor=vi  
set serveroutput on size 1000000  
set trimspool on  
set long 5000  
set linesize 300  
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  
 
说明：
 
（1）SET TRIMOUT ON SETTRIMSPOOL ON
 
SET TRIMOUT ON or SET TRIMSPOOL ON removes trailing blanks at the end of each displayed orspooled line.
 
Setting these variables ON can reduce the amount of datawritten. However, if LINESIZE is optimal, it may be faster to set the variablesOFF. The SQL*Plus output line is blank filled throughout the query processingroutines so removing the spaces could take extra effort.
 
Trimout与trimspool分别处理标准终端输出与spool输出结尾处的空格是否去掉。若on，则去掉空格。若off，则不去掉空格，按照linesize的大小补充空格数量。
 
默认情况下，trimout on, trimspool off。
 
（2）column plan_plus_expformat a80
 
 This sets the default width of the explainplan output we receive with AUTOTRACE. a80is generally wide enough to hold the full plan.
 
（3）set termout off
 
当设置为set termout off的时候就会忽略sql语句的输出如果这条sql是在脚本中运行的话。也就是说如果命令不在脚本中运行，即使设置为set termout off，仍然会返回sql的结果。
 
（4）设置SQL提示符
 
以下内容用于定义SQL提示符：
 
[sql] 
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 (selectglobal_name, instr(global_name,'.') dot from global_name );  
set sqlprompt'&gname> '  
set termout on  
 
The directive column global_name new_valuegname 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_nameout of the database, and concatenate this with the username I am logged inwith. That makes my prompt look like this
 
ops$tkyte@ora11gr2>
 
so I know who I am as well as where I am.
 
其实可简单改为：
 
[sql] 
select lower(user) || '@'||global_name  from global_name;  
 
即可得到以下结果：
sys@NFIRMS