关于login.sql
最近遇到一个问题,在sqlplus首次登录时,会调用login.sql,显示指定的SQLPROMPT,但是当再次使用connect命令连接其他用户时,显示的SQLPROMPT一直无法改变,而且重新connect之后所有在login.sql中设置的属性也全部丢失。找了很久原因之后才发现原来是因为Oracle的版本问题,9i中只能在初次登录sqlplus时才会去调用login.sql,而在10g之后,每次connect都会调用该脚本。
下面看一个转载的文章,说的比较清楚:注意红色粗体部分。
---------------------------------------------------------------------------------------------------------
Storing settings for SQL*PLUS (login.sql and glogin.sql)
glogin.sql
Whenever SQL*PLUS starts up, it looks for a file namedglogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed. This allows to store settings (such as linesize) accross SQL*PLUS sessions.
New in Oracle 10g: Oracle also reads glogin.sql and login.sql at a connect in sql*plus.
login.sql
Additionally, after reading glogin.sql, sql*plus also looks for a file named login.sql in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATHpoints to and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql
If the restriction level is set to 3, the login.sql is not read.
A common login.sql file
set pagesize 0
set linesize 190
define _editor=gvim
10g
Since Oracle 10g, the login.sql is executed after a connect.
This allows to have a prompt that displays the username.
For that, the following line must be in the login.sql:
set sqlprompt "&_user> "
------------------------------------------------------------------------------------------------------------
同时在10g官方文档《SQLPlus User's Guide and Reference》里也有记载,设置SQLPLUSCOMPATIBILITY可以改变sqlplus某些属性,其中就包括glogin/login的读取:
SET SQLPLUSCOMPATIBILITY {x.y[.z]}
Value Consequence When available
>=10.1 SHOW ERRORS sorts PL/SQL error messages using new 10.1
columns only available in Oracle Database 10g.
>=10.1 SPOOL Options CREATE, REPLACE, SAVE were added which 10.1
may affect filename parsing on some platforms.
>=10.1 SET SQLPROMPT 10.1
>=10.1 Whitespace characters are allowed in Windows file names that 10.1
are enclosed in quotes. Some other special punctuation characters
are now disallowed in Windows.
>=10.1 Glogin/login files are called for each reconnect. 10.1
<10.1 Uses the obsolete DOC> prompt when echoing /* comments. 10.1
>=9.2 A wide column defined FOLD_AFTER may be displayed at the 9.2.
start of a new line. Otherwise it is incorrectly put at the end
of thepreceding line with a smaller width than expected.
>=9.0 Whitespace before a slash ("/") in a SQL statement is ignored and 9.0.1.4.
the slash is taken to mean execute the statement. Otherwise the
slash is treated as part of the statement, for example, as a
divisionsign.
>=9.0 The length specified for NCHAR and NVARCHAR2 types is 9.0.1
characters. Otherwise the length may represent bytes or
characters depending on the character set.