ORA-02020: too many database links in use

[Powell, Mark]

Please see the following:

How do I find distributed queries / transactions (either issued from or connecting to this instance)? http://www.jlcomp.demon.co.uk/faq/find_dist.html


________________________________
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of Roger Xu
Sent: Friday, January 22, 2010 5:01 PM
To: oracle-l-***@public.gmane.org
Subject: ORA-02020: too many database links in use

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

[Jared Still]

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

Post by Powell, Mark
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

[Roger Xu]

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.

Post by Jared Still
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

Post by Powell, Mark
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

...

[Jared Still]

Post by Roger Xu
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.

The PID of the originating client is in the output of the query.

Here's the output from a database link target., A remote user has select *
from ***@dblink

08:23:40 ordb03.radisys.com - ***@dal3 SQL> l
1 Select /*+ ORDERED */
2 substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
3 substr(g.K2GTITID_ORA,1,35) "GTXID",
4 substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
5 s2.username,
6 substr(
7 decode(bitand(ksuseidl,11),
8 1,'ACTIVE',
9 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
10 2,'SNIPED',
11 3,'SNIPED',
12 'KILLED'
13 ),1,1
14 ) "S",
15 substr(w.event,1,10) "WAITING"
16 from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
17 where g.K2GTDXCB =t.ktcxbxba
18 and g.K2GTDSES=t.ktcxbses
19 and s.addr=g.K2GTDSES
20 and w.sid=s.indx
21* and s2.sid = w.sid
08:24:19 ordb03.radisys.com - ***@dal3 SQL> /

ORIGIN
-------------------------------------------------------------
GTXID
----------------------------------------------------------------------------------------------------
LSESSION USERNAME S WAITING
---------------------------- ---------- --- ------------------------------
ordevdb01.-*16642*
DBTRG.d6d6d69e.4.7.6296
634.3105 SCOTT I SQL*Net me


The source PID is 16642.

Now this query will identify the originating user on the remote database.


1 select
2 s.username,
3 s.sid,
4 s.serial#,
5 p.pid ppid,
6 s.status,
7 --s.machine,
8 --s.osuser,
9 --substr(s.program,1,20) client_program,
10 s.process client_process,
11 --substr(p.program,1,20) server_program,
12 p.spid spid,
13 to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
14 -- idle time
15 -- days added to hours
16 --( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
17 -- days separately
18 substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
19 -- hours
20 substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
21 -- minutes
22 substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':'
||
23 --seconds
24 substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2)
idle_time
25 from v$session s, v$process p
26 where s.username is not null
27 -- use outer join to show sniped sessions in
28 -- v$session that don't have an OS process
29 and p.addr(+) = s.paddr
30 -- uncomment to see only your own session
31 --and userenv('SESSIONID') = s.audsid
32* order by username, sid
08:32:32 ordevdb01.radisys.com - ***@dv14 SQL> /

CLIENT SRVR
USERNAME SID SERIAL# PID STATUS PID PID LOGON
TIME IDLE TIME
---------- ---- ------- ---- ---------- ------------------------ -----
----------------- -----------
SCOTT 510 13019 28 INACTIVE 16640
*16642*01/26/10 08:22:41 00:00:09:09
524 10105 27 INACTIVE 29209 10603
01/25/10 14:25:12 00:18:06:58

SYS 509 9136 31 ACTIVE 16834 16836
01/26/10 08:28:56 00:00:00:00

SYSTEM 500 10882 29 INACTIVE 12739 12844
01/25/10 14:56:13 00:17:36:17


4 rows selected.

The originator of the dblink connection is SCOTT.


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com