如何查出数据库中哪些SESSION在使用DBLINK？

Yong Huang

最初由 fjhello2002 发布
[B]如何查出数据库中
哪些SESSION通过DBLINK访问其它数据库？
哪些SESSION是其它数据库上通过DB_LINK连接进来的？ [/B]

Hope this thread helps.

http://groups.google.com/group/c ... ad/a7c87c2daeb82f95

Yong Huang

jametong

调整了一下....

column ORIGIN format a39
column GTXID format a39 heading "GLOBAL TXN ID"
column LSESSION format a10 heading "SID,SER#"
column WAITING format a55 heading "WAITING FOR EVENT"
column STATUS format a10 heading "STATUS"

Select /*+ ORDERED */
substr(s.ksusemnm,1,23)||'-'|| substr(s.ksusepid,1,15) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||','|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,10) "STATUS",
event "WAITING"
from sys.x$k2gte g, sys.x$ktcxb t, sys.x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and s.indx = w.sid

Yong Huang

最初由 jametong 发布
[B]调整了一下....

column ORIGIN format a39
column GTXID format a39 heading "GLOBAL TXN ID"
column LSESSION format a10 heading "SID,SER#"
column WAITING format a55 heading "WAITING FOR EVENT"
column STATUS format a10 heading "STATUS"

Select /*+ ORDERED */
substr(s.ksusemnm,1,23)||'-'|| substr(s.ksusepid,1,15) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||','|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,10) "STATUS",
event "WAITING"
from sys.x$k2gte g, sys.x$ktcxb t, sys.x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and s.indx = w.sid [/B]

This is an excellent query.

Just a friendly reminder. It's absolutely important to give credit to the original author, unless you claim to be the author. In this case, I find this query at

http://www.freelists.org/archives/oracle-l/02-2005/msg00613.html

where the message poster attributed to Mark Bobak, who posted the query in Metalink forum thread 524821.994. In there, Mark attributed the authorship to Tom Kyte. For obvious reason, Tom can't post that query at asktom.oracle.com.

Yong Huang

jametong

I have claimed i got it from the web.

Yong Huang

最初由 jametong 发布
[B]I have claimed i got it from the web. [/B]

Thanks. I just notice you said "从别的地方cp的". Sorry I missed that part earlier. Would be better if you could tell us the exact location in that message.

Yong Huang