I'm looking for a way to find out if there are uncommited INSERT, UPDATE or DELETE statements in the current session. One way would be to check v$lock with the current sid, but that requires read access to v$lock, which is a problem if the DBA doesn't want to grant it. Any other ways (other than keeping track of all database commands issued by the application)?
This question is related to
oracle
transactions
SELECT * FROM V$TRANSACTION
WHERE STATUS='ACTIVE';
See: http://forums.oracle.com/forums/thread.jspa?threadID=691061
Use the query below to find out pending transaction.
If it returns a value, it means there is a pending transaction.
Here is the query:
select dbms_transaction.step_id from dual
;
References:
http://www.acehints.com/2011/07/how-to-check-pending-transaction-in.html
http://www.acehints.com/p/site-map.html
The easiest and most reliable solution is to try and start a transaction and see it if succeeds. If some code already started a transaction but has not yet issued any DML, then the V$TRANSACTION view won't show anything.
In this example below, I handle the exception to raise a user-defined application error. To defer to an existing exception handler, just do a SET TRANSACTION and then immediately COMMIT to undo it.
DECLARE
transaction_in_progress EXCEPTION;
PRAGMA EXCEPTION_INIT(transaction_in_progress, -1453);
BEGIN
SET TRANSACTION NAME 'CHECK_FOR_TRANSACTION_ALREADY_SET';
COMMIT; -- end transaction
EXCEPTION
WHEN transaction_in_progress THEN
RAISE_APPLICATION_ERROR(-20000,'Transaction is already in progress');
END;
/
This is the query I normally use,
select s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;
Matthew Watson can be modified to be used in RAC
select t.inst_id
,s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
,t.start_time
from gv$transaction t
inner join gv$session s on t.addr = s.taddr;
Source: Stackoverflow.com