name: Mark D Powell
Author's Email: Mark.Powell@eds.com
written: 31 Oct 2002
Oracle version(s): 18.104.22.168
|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 22.214.171.124.
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 Kytes 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