CODEWITHSUNDEEP
oracle-databaseplsqloracle11gcommit
Felipe Vidal
Felipe Vidal

Reputation: 505

See changes of a different SQL Oracle session before COMMIT?

I coordinated the implementation of an Oracle PL/SQL DML script, but to do that in Production I usually would reach out to a DBA Team for them to run the script, provided they have the appropriate permissions.

Is there a way for my user to see the changes prior he actually COMMITs them? I know the changes will be active in the session that executed the script, but is there a way for a different session, & moreover, a different user to see them?

Upvotes: 2

Views: 2604

Answers (4)

Jon Heller
Jon Heller

Reputation: 36807

The package DBMS_XA can transfer an uncommitted transaction from one session to another. The second session can view the uncommitted data and can commit, rollback, or pass the transaction on again. The below example is based on this oracle-base article, and while it works for me, I have never used this feature in production before and there may be complications I'm not aware of.

First, create a table used by the example:

--DROP TABLE tab1;

CREATE TABLE tab1 (
  id  NUMBER
);

Session 1

Change the transaction timeout from 60 seconds to a larger value:

DECLARE
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_settimeout(500);
END;
/

Start a transaction, passing in a magic number to name the transaction (999):

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_start(xid  => l_xid,
                                   flag => DBMS_XA.TMNOFLAGS);
END;
/

Do the real work here, without committing:

INSERT INTO tab1 (id) VALUES (1);

End the transaction so another session can attach to it later:

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_end(xid  => l_xid,
                                 flag => DBMS_XA.TMSUSPEND);
END;
/

The data has disappeared from our session - this table does not include the row we just inserted. Another session will need to attach to the global transaction and either commit it or roll it back.

select * from tab1;

Session 2

Initially, this session cannot see the data:

select * from tab1;

Attach to the global transaction, using the same magic number (999):

DECLARE
  l_xid     DBMS_XA_XID := DBMS_XA_XID(999);
  l_return  PLS_INTEGER;
BEGIN
  l_return := SYS.DBMS_XA.xa_start(xid  => l_xid,
                                   flag => DBMS_XA.TMRESUME);
END;
/

Now only this session can see the new row:

select * from tab1;

The second session can commit, rollback, or pass the transaction to yet another session. To pass the transaction, call DBMS_XA.XA_END again and have another session then call DBMS_XA.XA_START.

Upvotes: 4

vishad
vishad

Reputation: 1164

It is not possible to view the uncommitted changes of one session in another session. However since you are running a PL/SQL DML script you can try putting in logging in the script to view the script progress. You would need to use a separate PL/SQL block with AUTONOMOUS transaction to log the data.

Upvotes: 2

mhaselup
mhaselup

Reputation: 238

No it is not possible, this is by design. By definition the ACID properties of RDBMS include "Isolation" which prevents individual connections from being aware of one another i.e. seeing the changes in another session.

Upvotes: 1

pmdba
pmdba

Reputation: 7033

There is no way for one session to see changes made by another until they are committed.

Upvotes: 2

Related Questions