How do I find distributed queries / transactions (either issued from or connecting to this instance)?

The Oracle (tm) Users' Co-Operative FAQ

How do I find distributed queries / transactions (either issued from or connecting to this instance)?

Back to index of questions

This FAQ entry will demonstrate SQL to locate sessions currently involved in distributed transactions in three forms: remote SQL issued from the instance, remote SQL issued from or to the instance, and remote transaction issued from other instances.

All examples ran on 64 bit RDBMS version 8.1.7.4.

Whenever a distributed query or DML statement is issued from an instance a Distributed Transaction lock, DX, is taken on the transaction and is visible through v$lock:

        select addr, kaddr, sid, type, id1 from v$lock where type = 'DX';
	no rows selected

	select count(*) from oper_console;	-- synonym on remote table
	COUNT(*)
	--------
	    6226

	select addr, kaddr, sid, type, id1 from v$lock where type = 'DX';
	ADDR             KADDR                   SID TY        ID1
	---------------- ---------------- ---------- -- ----------
        070000000015CC48 070000000015CC68         16 DX         29

This entry will remain until the issuer either commits or rollbacks the transaction.This means that a query on a remote object is a transaction and it takes a rollback segment entry to support the two-phase commit feature (see References). But while the above query on v$lock will show distributed queries from this instance to a remote database it does not show queries from the remote instance to this instance.

The only way I know to locate these queries from the (target) instance is the following query, which I based on an ASKTOM article (see references), and which requires the user to be logged on as SYS or to create a view on sys.x$k2gte and grant select on this view to the desired user(s).

        select
          username
         ,osuser
         ,status
         ,sid
         ,serial#
         ,machine
         ,process
         ,terminal
         ,program
        from
         v$session
        where saddr in ( select k2gtdses from sys.x$k2gte );

This query will show sessions that have issued queries using remote objects and queries issued from remote instances to this instance.

To find only those sessions coming from a remote source the change the where clause as follows (Eliminate DX transactions identified above):

        select
          s.username
         ,s.osuser
         ,s.status
         ,s.sid
         ,s.serial#
         ,s.machine
         ,s.process
         ,s.terminal
         ,s.program
        from
          v$session s
        where s.saddr in ( select x.k2gtdses from sys.x$k2gte x )
        and   not exists ( select
                           l.sid
                            from v$lock l
                           where l.type = 'DX'
                           and   l.sid  = s.sid
                          )
	;

WARNING - During testing this query was producing incorrect results with both not in and exists clauses until I added the table name label on X$K2GTE. When X$ tables (views) are involved it is probably wise to fully qualify all tables and columns.The behavior of any X$ table is subject to change without notice with every patch set / release of the Oracle RDBMS so regular verification of query results is advisable.  With a few exceptions Oracle does not document the X$ tables.

Further reading: see Tom Kyte’s article on this subject at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5309401983757 

See FAQ entry  Why does it seem that a SELECT over a db_link requires a commit after execution ?

Back to top

Back to index of questions