sqlplus如何查找当前连接的数据库会话的详细信息 [英] sqlplus how to find details of the currently connected database session
问题描述
我最近开始研究oracle数据库.通常,我有很多sqlplus窗口可以打开到不同的oracle数据库.从一个sqlplus会话切换到另一个sqlplus会话时,如何查看每个sqlplus会话的当前会话详细信息?任何帮助,我们将不胜感激!
I have very recently started to work on oracle databases. Generally I have many sqlplus windows open to different oracle databases. When switching from one sqlplus session to another, how can i view the current session details for each sqlplus session ? Any help is greatly appreciated!
推荐答案
看看这个 (c) Tanel Poder
.您可以从glogin.sql运行它(因此这些设置将在每次连接时更新,或者只是手动运行.请注意host title
命令-它更改带有会话信息的sql * plus控制台窗口标题-对于许多应用程序非常有用窗户同时打开.
Take a look at this one (c) Tanel Poder
. You may either run it from your glogin.sql (so these settings will update each time you connect, or just run it manually. Notice host title
command - it changes your sql*plus console window title with session information - extremely useful with many windows open simultaneously.
-- 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
这篇关于sqlplus如何查找当前连接的数据库会话的详细信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!