I have a loop in a procedure which calls a number of other procedures. At the end of the loop, it does a commit. However, based on the results I am seeing it appears it is doing a commit on each iteration in the loop.
I have manually searched the code and cannot find the commit. Is there any table or view which will tell me the procedure which contained the last commit executed?
, you can use the V$TRANSACTION view to obtain information about the current active transactions. Specifically, you can check the XIDUSN, XIDSLOT, and XIDSQN columns from the view to identify the transaction associated with a specific commit.
Here's how you can check which procedure contains the last commit executed:
SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec, t.status FROM v$session s JOIN v$transaction t ON s.saddr = t.ses_addr WHERE s.username = '<YOUR_USERNAME>';
Replace <YOUR_USERNAME> with the appropriate username of the session that is executing the procedure.
Note down the SID and SERIAL# of the session that is performing the commits.
Next, find the SQL statements associated with that session (including commits):
Look for the SQL_TEXT that includes the COMMIT statement. The SQL_ID and PREV_SQL_ID columns will help you track the sequence of executed SQL statements.
