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

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

.

Relevant Pages

  • Re: Form Arrays
    ... I'm a new person when it comes to PHP and I have a quick question. ... enter the information to the database until they are done. ... You issue a tag id when the header form is created, and carry it as a post variable through all the session. ... If they switch off and walk away, the transaction isn't marked as complete, and the data can be erased sometime later. ...
    (comp.lang.php)
  • Re: J2EE - entities - When do JPA entity units get saved into the database
    ... as JPA entities and EJB 2 Entity Beans have ... remote stub pointing to a server object. ... field changes back to the database if the changes occur within the ... same transaction that the entity was loaded in AND if the object has ...
    (comp.lang.java.programmer)
  • Re: J2EE - entities - When do JPA entity units get saved into the database
    ... as JPA entities and EJB 2 Entity Beans have ... serialized as-is and returned across remote calls. ... field changes back to the database if the changes occur within the ... same transaction that the entity was loaded in AND if the object has ...
    (comp.lang.java.programmer)
  • Re: More than pessimistic record locking needed...
    ... I assume that each http session has a separate thread, ... database with highly isolated transactions... ... With the correct locking inside a transaction SQL Server places an exclusive ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Single database transaction across a multiple HTTP requests?
    ... when I go to a page with a valid Cookie session I want ... >>> to be able to retrieve the same database connection I opened ... > more suited to a dedicated client/server model or batch submission (e.g. ... > then assemble a 'batch file' which is used to do the transaction, ...
    (comp.lang.php)