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

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 26 Jan 2010 08:34:11 -0800
Message-ID: <bf46381001260834q3f85e745uc9583808f764db5c_at_mail.gmail.com>

On Tue, Jan 26, 2010 at 7:32 AM, Roger Xu <wellmetus_at_gmail.com> 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_at_dblink

08:23:40 ordb03.radisys.com - sys_at_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_at_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_at_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

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

Received on Tue Jan 26 2010 - 10:34:11 CST