Re: ORA-02020: too many database links in use
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Roger Xu <wellmetus@xxxxxxxxx>
- Date: Tue, 26 Jan 2010 08:34:11 -0800
On Tue, Jan 26, 2010 at 7:32 AM, Roger Xu <wellmetus@xxxxxxxxx> wrote:
> 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 dual@dblink
08:23:40 ordb03.radisys.com - sys@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 - sys@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 - sys@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
Other related posts:
