I can use this script to find the session which has a open db link. Now how do I find out which db link? In other words, how do I query other session's v$dblink? Thanks. On Mon, Jan 25, 2010 at 7:36 PM, Jared Still <jkstill@xxxxxxxxx> wrote: > Similar to what Mark Powell, sent, here's a query I got from > Mark Bobak, based one pretty much the same tables. > > -- who_dblink.sql > -- who is querying via dblink? > -- Courtesy of Tom Kyte, via Mark Bobak > -- this script can be used at both ends of the database link > -- to match up which session on the remote database started > -- the local transaction > -- the GTXID will match for those sessions > -- just run the script on both databases > > Select /*+ ORDERED */ > substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN", > substr(g.K2GTITID_ORA,1,35) "GTXID", > substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" , > s2.username, > substr( > decode(bitand(ksuseidl,11), > 1,'ACTIVE', > 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), > 2,'SNIPED', > 3,'SNIPED', > 'KILLED' > ),1,1 > ) "S", > substr(w.event,1,10) "WAITING" > from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2 > where g.K2GTDXCB =t.ktcxbxba > and g.K2GTDSES=t.ktcxbses > and s.addr=g.K2GTDSES > and w.sid=s.indx > and s2.sid = w.sid > / > > > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > Oracle Blog: http://jkstill.blogspot.com > Home Page: http://jaredstill.com > > > > > On Fri, Jan 22, 2010 at 2:00 PM, Roger Xu <wellmetus@xxxxxxxxx> wrote: > >> We are getting this error and I know I need to increase open_links >> parameter which requires recycling the instance. My question is - how to >> find out all open dblinks so I can kill some sessions which are holding >> dblinks? (V$DBLINK lists all open database links in the current session.) >> Thanks, Roger > > >