oracle数据库会话SQL,oracle – sqlplus如何查找当前连接的数据库会话的详细信息

看看这个

(c) Tanel Poder.你可以从你的glogin.sql运行它(所以这些设置会更新每次你连接,或者只是手动运行.注意主机标题命令 – 它改变您的sql *加控制台窗口标题与会话信息 – 与许多窗口同时打开非常有用.

-- the Who am I script

def mysid="NA"

def _i_spid="NA"

def _i_cpid="NA"

def _i_opid="NA"

def _i_serial="NA"

def _i_inst="NA"

def _i_host="NA"

def _i_user="&_user"

def _i_conn="&_connect_identifier"

col i_username head USERNAME for a20

col i_sid head SID for a5 new_value mysid

col i_serial head SERIAL# for a8 new_value _i_serial

col i_cpid head CPID for a15 new_value _i_cpid

col i_spid head SPID for a15 new_value _i_spid

col i_opid head OPID for a5 new_value _i_opid

col i_host_name head HOST_NAME for a25 new_value _i_host

col i_instance_name head INST_NAME for a12 new_value _i_inst

col i_ver head VERSION for a10

col i_startup_day head STARTED for a8

col _i_user noprint new_value _i_user

col _i_conn noprint new_value _i_conn

col i_myoraver noprint new_value myoraver

select

s.username i_username,

i.instance_name i_instance_name,

i.host_name i_host_name,

to_char(s.sid) i_sid,

to_char(s.serial#) i_serial,

(select substr(banner, instr(banner, 'Release ')+8,10) from v$version where rownum = 1) i_ver,

(select substr(substr(banner, instr(banner, 'Release ')+8),

1,

instr(substr(banner, instr(banner, 'Release ')+8),'.')-1)

from v$version

where rownum = 1) i_myoraver,

to_char(startup_time, 'YYYYMMDD') i_startup_day,

p.spid i_spid,

trim(to_char(p.pid)) i_opid,

s.process i_cpid,

s.saddr saddr,

p.addr paddr,

lower(s.username) "_i_user",

upper('&_connect_identifier') "_i_conn"

from

v$session s,

v$instance i,

v$process p

where

s.paddr = p.addr

and

sid = (select sid from v$mystat where rownum = 1);

-- Windows CMD.exe specific stuff

-- host title %CP% &_i_user@&_i_conn [sid=&mysid ser#=&_i_serial spid=&_i_spid inst=&_i_inst host=&_i_host cpid=&_i_cpid opid=&_i_opid]

host title %CP% &_i_user@&_i_conn [sid=&mysid #=&_i_serial]

-- host doskey /exename=sqlplus.exe desc=set lines 80 sqlprompt ""$Tdescribe $*$Tset lines 299 sqlprompt "SQL> "

-- short xterm title

-- host echo -ne "\033]0;&_i_user@&_i_inst &mysid[&_i_spid]\007"

-- long xterm title

--host echo -ne "\033]0;host=&_i_host inst=&_i_inst sid=&mysid ser#=&_i_serial spid=&_i_spid cpid=&_i_cpid opid=&_i_opid\007"

def myopid=&_i_opid

def myspid=&_i_spid

def mycpid=&_i_cpid

-- undef _i_spid _i_inst _i_host _i_user _i_conn _i_cpid

样品输出：

17:39:35 SYSTEM@saz-dev> @sandbox

Connected.

18:29:02 SYSTEM@sandbox> @me

USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR

-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- -------- --------

SYSTEM xe OARS-SANDBOX 34 175 11.2.0.2.0 20130318 3348 30 6108:7776 6F549590 6FF51020

1 row selected.

Elapsed: 00:00:00.04