RE: Finding open database links database wide

  • From: John Kanagaraj <john.kanagaraj@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 14 Feb 2005 10:25:44 -0800

>Well, that's one heck of a neat trick, except it does not tell 
>you which
>user has which link open.  Is there a way to catch that??=20

Dick, The follow SQL will help.

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" ,
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
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)   - Manuals for DBAs (English only) - Manual for Life (in English, Deutsch, French,
Italian, Spanish, Portugese, Turkish,...)

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

Other related posts: