Is there a way to look inside another Oracle session?
I have a query editor ( Toad ) looking at a database.
At the same time, I am also debugging the application with my own separate connection.
My application starts a transaction, performs some updates, and then makes decisions based on some SELECT statements. Since the update statements (which are many and complex) have not yet been executed, the results my application gets from the SELECT are not the same as what I get if I run the same statement in Toad.
I currently work around this by dumping the request output from the application to a text file and reading this.
Is there a better way to look inside another oracle session and see what that session sees before the commit completes?
Another way to ask the question is, in Oracle, is it possible to enable dirty reads between two sessions without affecting anyone else in the session?
source to share
No, Oracle does not allow dirty reads. Also, since the changes may not have been physically written to disk, you won't find them in data files. The journalist records any pending data changes at least every three seconds, so you can use the Log Miner stuff to pick it there.
But in general, it's best to include your own debug information, which you can easily turn on and off as needed.
source to share
This is not a complete answer that I know, but there are no dead reads yet, there are locks that can give you an idea of what is going on.
In session 1, if you insert a row with primary key 7, you won't see it when you select from session 2. (That would be a dirty read).
However, if you try to insert from session 2 using the primary key of 7, then it will block behind session 1 as it has to wait and see if session 1 commits or rolls back. You can use "WAIT 10" to wait 10 seconds for this to happen.
A similar story exists for updates or anything that can cause a unique constraint violation.
source to share
Can't you just set the isolation level on the session where you want to max out to "read unfixed" with a session change command or a login trigger (I haven't tried this myself) temporarily?
What I prefer to do (in general) is debug statements in the code that stay there permanently but are disabled during production - the Tom Kyte debug.f package is a useful place to start - http://asktom.oracle. com / tkyte / debugf
source to share