[2022-03-21] sqlplus 显示格式化

sqlplus登录之后默认的显示前缀为

SQL> 

如果想把前缀进行修改，可以设置sqlprompt变量的值，可以设置当前已经定义的变量值

SQL > define
DEFINE _DATE           = "21-MAR-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)
SQL >

例子,使用_USER 和 _CONNECT_IDENTIFIER设置成显示前缀

SQL> set sqlprompt "_USER@ _CONNECT_IDENTIFIER >"
SYS@ db1 >

设置时间显示

SYS@ db1 >set time on
11:14:47 SYS@ db1 >
11:14:51 SYS@ db1 >

sqlplus登录时，会首先调用$ORACLE_HOME/sqlplus/admin/glogin.sql， 可以把需要客户化的数据提前写到该文件，比如在文件添加如下内容(显示格式：时间+用户+实例+主机名)：

set time on
set termout off
column sqlprompt_q new_value sqlprompt_q
select upper(user)||'@'|| instance_name||'('||host_name||')' as sqlprompt_q from v$instance;
set sqlprompt '&sqlprompt_q> '

登录数据库显示如下：

[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 11:17:43 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

11:17:43 SYS@db1(11g)> 
11:17:48 SYS@db1(11g)> 
11:17:49 SYS@db1(11g)>