sql – Is it possible to see changes in another session before COMMIT in it?

Question:

I have implemented a PL/SQL script. In a production environment, the script will start the DBA.

Is there a way to see the changes the script made before the COMMIT is done?

I know that the changes will be active in the session in which the script was executed. But maybe there is a way in another session, or moreover, with another user, to see these changes?

---

_{Free translation of the question See changes of a different SQL Oracle session before COMMIT? by @Felipe Vidal Otero}

Answer:

The DBMS_XA package can pass an uncommitted transaction from one session to another. Another session may view uncommitted data, may commit, rollback, or resubmit it to another transaction. The example below is based on this ORACLE-BASE article and looks to work, but has never been tested in a production environment so at your own risk.

Session 1

Test table and changing the timeout for branching transactions (by default 60 sec.):

create table t1 (id number);

var ret number
exec :ret := dbms_xa.xa_settimeout(600);

Start a transaction with transaction magic number (9999) and insert the first record:

exec :ret := sys.dbms_xa.xa_start (xid=>dbms_xa_xid (9999), flag=>dbms_xa.tmnoflags);

insert into t1 (id) values (1);

Let's complete the transaction so that another session can be connected to it. The entry that was just inserted will no longer be available in this session:

exec :ret := sys.dbms_xa.xa_end (xid=>dbms_xa_xid (9999), flag=>dbms_xa.tmsuspend);

select * from t1;

no rows selected

Session 2

Initially, this session cannot see the data:

select * from t1;

no rows selected

Connect to a global transaction using the same magic number (9999). Then, only that session can see the new entry. Finally, this session must commit, rollback, or again – detach from the transaction:

var ret number
exec :ret := dbms_xa.xa_start (xid=>dbms_xa_xid (9999), flag=>dbms_xa.tmresume);

select * from t1
/

        ID
----------
         1

begin 
    :ret := dbms_xa.xa_end (xid=>dbms_xa_xid (9999), flag=>dbms_xa.tmsuccess);
    if :ret = dbms_xa.xa_ok then 
        :ret := dbms_xa.xa_commit (xid=>dbms_xa_xid (9999), onePhase=>true);
    end if;
end;
/

---

_{Loose translation of answer from member @Jon Heller}