Re: ORA-02020: too many database links in use
- From: Jared Still <jkstill@xxxxxxxxx>
- To: wellmetus@xxxxxxxxx
- Date: Mon, 25 Jan 2010 17:36:01 -0800
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
Other related posts:
