[20160317]sqlplus提示上顯示sid,serial#

[20160317]sqlplus提示上顯示sid,serial#.txt

--工作與測試需要,需要修改sqlplus命令列提示,加入session的sid與serial#,花一點時間寫一個指令碼:
--一般預設顯示的是SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '.

$ cat s.sql
--SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
set feedback off
set termout off
column v_sid new_value v_sid format a10
select s.sid||','||s.serial# v_sid from v$session s where s.sid in (select sid from v$mystat where rownum=1) ;
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.(&v_sid)> '
column v_sid clear
set termout on
set feedback 6

--測試OK:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @ &r/s
SCOTT@book(84,63)>

SCOTT@book(84,63)> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        84         63 65418       31        153 alter system kill session '84,63' immediate;

SCOTT@book(84,63)> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON