Re: ORA-02020: too many database links in use

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 25 Jan 2010 17:36:01 -0800
Message-ID: <bf46381001251736p5633399al727f161aca39ff61_at_mail.gmail.com>

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 25 2010 - 19:36:01 CST