我们使用sqlplus的时候,他的提示符大都是 “SQL >”这个样子的,我们可以通过SQLPROMPT来改变提示符,为我们提供更人性化的信息,在10g之前这一点做的不好
SPORTS@ dbtest>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SPORTS@ dbtest>
SPORTS@ dbtest>define
DEFINE _DATE = "04-JUN-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "dbtest" (CHAR)
DEFINE _USER = "SPORTS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
上面有很多预定义的宏变量,下面就简单修改下sqlprompt:
SYS@ dbtest>set sqlprompt "_USER@ _CONNECT_IDENTIFIER as _PRIVILEGE>"
SYS@ dbtest as AS SYSDBA>
如果要每次登陆数据库都有此提示,需要把如下的内容添加到文件$ORACLE_HOME/sqlplus/admin/glogin.sql中,如下:
set sqlprompt "_user@ _connect_identifier>"
上面是在10g的环境,如果是9i,也可以实现这个功能,但是在“sql >”提示符下转换用户时,提示符是无法跟着改变的.
例如:
SPORTS@orcl> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SPORTS@orcl>
SPORTS@orcl> define
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000400" (CHAR)
要让提示符的修改永久生效,就修改文件$ORACLE_HOME/sqlplus/admin/glogin.sql,添加如下内容:
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT USER || '@' || NVL('&_CONNECT_IDENTIFIER', global_name )
sqlprompt
FROM global_name;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
下面就测试下,如下:
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jun 4 15:29:14 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SYS@orcl>
可以看到提示符已经按着我们意愿改变了啊,但是如多要用conn命令转变用户,提示符是不会转变的
SYS@orcl> conn sports/sports245
Connected.
SYS@orcl>
但是在10g中就可以的,用过conn命令转变用户身份,提示符也会随着变化的。
--转自