Friday, March 05, 2010

Who's using a database link?

Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It's one of those things that you may not need very often, but when you do need it, it is usually rather important.

Unfortunately for those of us charged with the care and feeding of the Oracle RDBMS, this information is not terribly easy to track down.

Some years ago when I first had need to determine which sessions were at each end of database link, I found a script that was supplied courtesy of Mark Bobak. When asked, Mark said he got the script from Tom Kyte. Yong Huang includes this script on his website, and notes that Mark further attributed authorship in Metalink Forum thread 524821.994. Yong has informed me that this note is no longer available.  I have found the script in other locations as well, such Dan Morgan's website. So now you know the scripts provenance.

Here's the script, complete with comments.  Following the script is an example of usage.


-- 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


Now let's take a look a the results of the script.

Logging on to DB1 as system, create a database link to db2 using the SCOTT account:

create database link scott_link connect to scott identified by "tiger" using 'db2';

Make sure it works:

system@db1 SQL> select sysdate from dual@scott_link;

SYSDATE
-------------------
03/05/2010 10:13:00

1 row selected.


Now logon to DB1 as sysdba and run who_dblink.sql:

sys@db1 SQL> @who_dblink

ORIGIN                GTXID                               LSESSION   USERNAME   S WAITING
--------------------- ----------------------------------- ---------- ---------- - ----------
oraserver.-21901      DB1.d6d6d69e.3.16.7190              500.15059  SYSTEM     I SQL*Net me

1 row selected.

Now do the same on DB2:

sys@db2 SQL> @who_dblink

ORIGIN                GTXID                               LSESSION   USERNAME   S WAITING
--------------------- ----------------------------------- ---------- ---------- - ----------
ordevdb01.-21903      DB1.d6d6d69e.3.16.7190             138.28152  SCOTT      I SQL*Net me

1 row selected.

How do you identify the session on the database where the database link connection was initiated?

Notice that the output from DB1 shows the PID in the ORIGIN column.  In this case it is 21901.

Running the following SQL on DB1 we can identify the session from which the SYSTEM user initiated the database link connection:

select
   b.username,
   b.sid SID,
   b.serial# SERIAL#,
   b.osuser,
   b.process
from v$session b,
     v$process d,
     v$sess_io e,
     v$timer
where
   b.sid = e.sid
   and  b.paddr = d.addr
   and  b.username is not null
   -- added 0.0000001 to the division above to
   -- avoid divide by zero errors
   -- this is to show all sessions, whether they
   -- have done IO or not
   --and  (e.consistent_Gets + e.block_Gets) > 0
   -- uncomment to see only your own session
   --and userenv('SESSIONID') = b.audsid
order by
         b.username,
         b.osuser,
         b.serial#,
         d.spid,
         b.process,
         b.status,
         e.consistent_Gets,
         e.block_Gets,
         e.Physical_reads;


USERNAME      SID SERIAL # OS USER              PROCESS
---------- ------ -------- -------------------- ------------------------
SYS           507    12708 oracle               22917

SYSTEM        500    15059 oracle               21901

2 rows selected.

The session created using the SCOTT account via the database link can also be seen using the same query on DB2 and looking for the session with a PID of 21903:

USERNAME    SID SERIAL# OSUSER  PROCESS
---------- ---- ------- ------- ------------
SCOTT       138   28152 oracle  21903
SYS         147   33860 oracle  22991
            146   40204 oracle  24096


3 rows selected.

16 comments:

Yong Huang said...

Jared, when Tom (and Mark and then you in 2005) originally publicized this query, v$session didn't have event column. So v$session_wait was needed. Now we can drop it from the "from" clause. For convenience, here's the simplified query:

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(s2.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and s2.sid=s.indx;

Jared said...

Thanks for the update Yong.

FYI for readers, the new version of the query works on 10g+. The older version is still required for 9i.

khair said...

Excellent article

just a mistypo, the sid must be 21901 and not 21903

I was wondering if there is a way to combine this information to measure the traffic in terms of bytes sent/received per session to drill down the "SQL*Net more data via dblink" statistics


Best Regards

Jared said...

Typo? Please provide more detail. After re-reading it, I don't see the typo you refer to.

khair said...

my apologies

i thought you wrote 21903 for DB1 database but it was 21901 (correct)


Best Regards
kostas

David O'Brien said...

Jared

I incorporated this query into a view (called v_dblinks) for use in a logon trigger to prevent access via a dblink and couldn't get the trigger to work.

If I run a query from a remote database, e.g. select * from v_dblinks@mydb, I can see the data for the logon generated by the dblink.

I included the same query, select * from v_dblinks, within a logon trigger on the local database

When the trigger is fired by the logon generated by the dblink, no data is returned.

Any ideas where I might look?

Thanks,
Dave

Jared said...

I guess the first thing I would do is make sure the trigger is firing.
Do you know that to be the case?

David O'Brien said...

Hi Jared,

The trigger is firing.

I was thinking that the problem relates to x$k2gte, x$ktcxb and x$ksuse; are the relevant records only added once the trigger has completed?

My knowledge of this area is somewhere between nil and none

Thanks,
Dave

Jared said...

That could be true. I wish I had time to test it right now, as it appears interesting.

Is this an 'after logon' trigger?

At this point I would suggest you pose the question on Oracle-L or some similar forum, complete with a test case that others can use to replicate the issue.

David O'Brien said...

Jared,

Thanks for the pointer to Oracle-L, it's new to me.

Keep Rambling,
Dave

Jared said...

Here's a link to Oracle-L registration:

Oracle-L Registration Page

Oracle-L Archive

Robson said...

Remember always that in the case to be used at a RAC system, the column inst_id from the X$ tables will be all fill only with the inst_id from the instance where you connected to.

Marcus Vale said...

It looks to me as if the PROCESS column in the V$SESSION view of the remote database has the same value as the SPID column in the V$PROCESS view of the local database. In my case, I don't have access to the tables in the original query, but I can see those views. Can I use this observation?

Jared said...

Hi Marcus,
Don't make me dig for it, as I don't see it on the page. :)
Can you point out just where you are seeing this, or provide an example?

Marcus Vale said...

V$SESSION.PROCESS VARCHAR2(12) Operating system client process ID on the remote session would contain 21901 in your example matching V$PROCESS.SPID VARCHAR2(12) Operating system process identifier. I'm seeing the values these days in an Oracle 9 database on an HP-UX server connecting to an Oracle 11 database on an AIX server. We have links in both directions and the process ids on the boxes are dramatically different.

Marcus Vale said...

Example:
I created two databases, sid ts00 and ts01, with a dblink from ts00 to ts01 using the id TS00_TS01. Using the linux id marcvale, I started sqlplus and executed:
SQL> select process from v$session@ts01 where username='TS00_TS01';

PROCESS
------------------------
7688
I then looked up the process id from linux:
[oracle@marcvale bin]$ ps -fp 7688
UID PID PPID C STIME TTY TIME CMD
oracle 7688 7686 0 16:04 ? 00:00:00 oraclets00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@marcvale bin]$ ps -fp 7686
UID PID PPID C STIME TTY TIME CMD
marcvale 7686 7399 0 16:04 pts/1 00:00:00 sqlplus