Inspired by the question on DBA stackexchange (which doesn’t have an easy answer),
Oracle – I’m debugging through a batch proess currently that does a lot of DML statements, but doesn’t do a commit right away. It would be nice to be able to view the “pending” changes while the transaction is not commited. Is this possible?
I’d like to share some SQL to view “pending” changes in Oracle.
Basically, they are based on the built-in views such as v$transaction and v$session .
SELECT s.sid, s.username, s.osuser, s.machine, s.status, s.program, t.start_time FROM v$session s, v$transaction t WHERE s.saddr = t.ses_addr ;
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.status, t.start_time FROM v$session s, v$transaction t WHERE s.saddr = t.ses_addr --AND s.sid in (select m.sid from v$mystat m) ;
SELECT * FROM v$session v WHERE v.AUDSID = userenv('sessionid') AND v.TADDR IS NOT NULL ;
select s.sid, s.serial#, s.status, s.osuser, s.machine, d.owner, d.object_name, d.object_type from v$session s, dba_objects d, v$locked_object l where s.sid = l.session_id AND d.object_id = l.object_id ;
Select dbms_transaction.local_transaction_id from dual;
UPDATED: Some ppl may wonder why I don’t post directly my “answers” to SE site. Well, I don’t want to contribute for StackOverflow or StackExchange sites.
I already mentioned this in a few posts, but the below comment help me edit this post to make it clear now. The SO/SE site owners got enough people to contribute answers/knowledge there already. As a result the sites has big reputation, and the owners got probably very, very much money with it. Why do I have to contribute to such guys, while I can contribute to other places (such as: mailing-list, support forum, … or even my own website), which I have been doing in several years?
See my post in the comments for a longer explanation.