oracle 配置 sqlplus,sqlplus环境配置(login.sql)-Oracle

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