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" ,
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
) "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;

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:

   b.sid SID,
   b.serial# SERIAL#,
from v$session b,
     v$process d,
     v$sess_io e,
   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

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

---------- ---- ------- ------- ------------
SCOTT       138   28152 oracle  21903
SYS         147   33860 oracle  22991
            146   40204 oracle  24096

3 rows selected.

Tuesday, March 02, 2010

Treasure Trove of Oracle Security Documents

This morning I stumbled across a veritable treasure trove of Oracle Security documents at the web site of the Defense Information Systems Agency.

I've only spent a few minutes glancing through a small sampling of them, and they appear to be fairly comprehensive. When you consider that these are documents used to audit sensitive database installations, it makes a lot of sense that they would cross all the t's and dot all the i's.

The index of documents can be found here: DISA IT Security Documents

As you can see for yourself, there are documents covering security concerns for a number of other systems.