Re: remote sessions (database link, v$session)...

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 15 Dec 2005 08:55:33 -0800
Message-ID: <1134665733.825542.52920@o13g2000cwo.googlegroups.com>

IDJ, if I understand what you want it is for session A running on database A to be able to know what v$session.sid was created on database B to support the distributed transaction issued from A.

Finding this from a third session on either database is one thing but if you want the information back in session A as definded above then I think you can test the following idea in more detail.

The remote code will have to be a pl/sql code object (procedure, function, package) and will issue a call to dbms_transaction.local_transaction_id. It will then take the transaction id which is really the rollback segment number, slot, and sequence and find it in v$transaction from which a join to v$session can be done. The remote SID could be passed back. A second remote DML statement issued from the same database A session should be part of the same transaction until a commit or rollback is issued.

You will need to test to see if this will work. On local instance. UT1 > select dbms_transaction.local_transaction_id   2 from sys.dual;

LOCAL_TRANSACTION_ID

---

• now issue remote query UT1 > select count(*) from chassis_no;

  COUNT(*)

---

        42

• we now have a LOCAL transaction id UT1 > select dbms_transaction.local_transaction_id from sys.dual;

LOCAL_TRANSACTION_ID

---

4.39.184176

Now create function on remote db.

drop function tid;
create function tid return varchar2
as
t_id varchar2(18);
begin
select dbms_transaction.local_transaction_id into t_id from sys.dual; return t_id;
end;
/

• now call it from local db ut1 > select mpowel01.tid_at_put.world from sys.dual;

TID

---

3.12.83492

ut1 > select count(*) from chassis_no;

  COUNT(*)

---

        42

• check to see we get same transaction number ut1 > select mpowel01.tid_at_put.world from sys.dual;

TID

---

3.12.83492

• verify local transaction number different ut1 > select dbms_transaction.local_transaction_id from sys.dual;

LOCAL_TRANSACTION_ID

---

4.16.184337

HTH -- Mark D Powell -- Received on Thu Dec 15 2005 - 10:55:33 CST