oracle sqlplus 如何查找当前连接的数据库会话的详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15541990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlplus how to find details of the currently connected database session
提问by Nishant Bhardwaj
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!
我最近开始研究 oracle 数据库。通常我有很多 sqlplus 窗口打开到不同的 oracle 数据库。从一个 sqlplus 会话切换到另一个会话时，如何查看每个 sqlplus 会话的当前会话详细信息？任何帮助是极大的赞赏！
采纳答案by Kirill Leontev
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 titlecommand - it changes your sql*plus console window title with session information - extremely useful with many windows open simultaneously.
看看这个(c) Tanel Poder。你可以从你的 glogin.sql 运行它（所以这些设置会在你每次连接时更新，或者只是手动运行它。注意host title命令 - 它用会话信息更改你的 sql*plus 控制台窗口标题 - 在许多窗口同时打开时非常有用.
-- 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 "3]0;&_i_user@&_i_inst &mysid[&_i_spid]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
7"
-- long xterm title
--host echo -ne "3]0;host=&_i_host inst=&_i_inst sid=&mysid ser#=&_i_serial spid=&_i_spid cpid=&_i_cpid opid=&_i_opidshow user
7"
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
Sample output:
示例输出：
select instance_name from v$instance
回答by igr
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
to get connected user
获取连接用户
select sys_context('USERENV','INSTANCE_NAME') from dual;
to get instance or set in sqlplus
在 sqlplus 中获取实例或设置
select instance_name from v$instance;
回答by rajesh
I know this is an old question but I did try all the above answers but didnt work in my case. What ultimately helped me out is
我知道这是一个老问题，但我确实尝试了上述所有答案，但在我的情况下没有用。最终帮助我的是
SHOW PARAMETER instance_name
SHOW PARAMETER instance_name
回答by Saurabh Tyagi
select * from v$session where sid = SYS_CONTEXT('USERENV','SID');
&
&
SELECT * FROM global_name;
will give you the instance name.
You can also use select * from global_name;to view the global name of the instance.
会给你实例名称。您还可以使用select * from global_name;来查看实例的全局名称。
回答by A.B.Cade
Try:
尝试：
select * from v$session
where sid = to_number(substr(dbms_session.unique_session_id,1,4),'XXXX')
You might also be interested in this AskTom post
您可能也对这篇 AskTom 帖子感兴趣
After seing your comment, you can do:
看到您的评论后，您可以：
select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| ' DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| ' Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
' OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| chr(10)||
' Client Program Name : '||s.program "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and s.sid = nvl('&SID',s.sid)
and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
and s.process = nvl('&Process',s.process)
and p.spid = nvl('&spid',p.spid)
and s.username = nvl('&username',s.username)
and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/
回答by Egor Skriptunoff
回答by Sapna
We can get the details and status of sessionfrom below query as:
我们可以从以下查询中获取会话的详细信息和状态：
##代码##For more details: https://ora-data.blogspot.in/2016/11/query-session-details.html
更多详情：https: //ora-data.blogspot.in/2016/11/query-session-details.html
Thanks,
谢谢，
