Does COMMIT show up in "Open Cursors" of Session Browser?

Toad for Oracle & DevOps Toolkit
1

Does COMMIT show up in "Open Cursors" of Session Browser?
Open Cursors has all the past SQLs that were executed for this session ID, I never see COMMIT in it. Is there a way to turn on something that will make COMMIT show up in this list?

2

COMMIT isn't a cursor, so there is no way to make a commit show up there.

It sounds like you want a replay of what a session did. If so, you should trace the session. Then you can use Trace File Browser or TKProf to see everything that the session did. Of course you have to start the trace before the session starts the work that you want to record, so maybe it is too late.

3

It's not all of the past SQLs, by the way, see V$OPEN_CURSOR

4

Thanks John for the response.
The reason I am looking for COMMIT is this session is a blocker of other sessions. Check out the below Open Cursors. 1 being the latest and 4 being the oldest

  1. SELECT from abc
  2. INSERT INTO pqr
  3. SELECT from user$..
  4. UPDATE XYZ..

the current session is blocking other 15 sessions. But the current session is only doing SELECT by looking at "Current Statement". I want to know if this session has commited #2 and #4. Make sense?

5

yes. If there are open transactions, you can see them on the RBS Usage tab of Session Browser, and also on the Locks tab.
If the open transactions are blocking other users, then on the LOCKS tab, then you can find the sessions doing the blocking and those getting blocked by selecting the session holding the lock at the top and seeing what's being blocked at the bottom.

I've set up a lock on my DB. Here's a screen shot of what I mean. The COMP1 user (1) is blocking the jdorlon user (2) in the SQLPlus session.

image
image1626×808 133 KB

It's perhaps easier to see here:

image
image1409×821 62.4 KB

Note: Looks like there is a bug here too...the OWNER/OBJECT_TYPE/OBJECT_NAME columns on the rows with Lock Type = "Transaction" are wrong. Those values are only correct for the rows of lock type DML. I'll fix.

I don't know if it's possible to see the details of the transaction. That info may be deep in Oracle's data dictionary somewhere, but there isn't a way to see that info in Toad.

6

Thank you, John. This is useful.

1 Like