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

From: Roger Xu <wellmetus_at_gmail.com>
Date: Tue, 26 Jan 2010 09:32:12 -0600
Message-ID: <eb64345d1001260732g7fd30d98h673e9ec25a4c6506_at_mail.gmail.com>

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_at_gmail.com> 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_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 Tue Jan 26 2010 - 09:32:12 CST