Is there a way to peek inside of another Oracle session?

Question

I have a query editor (Toad) looking at the database.

At the same time, I am also debugging an application with its own separate connection.

My application starts a transaction, does some updates, and then makes decisions based on some SELECT statements. Because the update statements (which are many and complex) are not committed yet, the results my application gets from its SELECT are not the same as what I get if I run the same statement in Toad.

Currently I get around this by dumping the query output from the app into a text file, and reading that.

Is there a better way to peek inside another oracle session, and see what that session sees, before the commit is complete?

Another way to ask this is: Under Oracle, can I enable dirty reads between only two sessions, without affecting anyone else's session?

Answer 1

Can you not just set the isolation level in the session you want to peak at to 'read uncommitted' with an alter session command or a logon trigger (I have not tried this myself) temporarily?

What I prefer to do (in general) is place debug statements in the code that remain there permanently, but are turned off in production - Tom Kyte's debug.f package is a useful place to start - http://asktom.oracle.com/tkyte/debugf

Answer 2

No, Oracle does not permit dirty reads. Also, since the changes may not have physically been written to disk, you won't find them in the data files. The log writer will write any pending data changes at least every three seconds, so you may be able to use the Log Miner stuff to pick it out from there.

But in general, your best bet is to include your own debugging information which you can easily switch on and off as required.

Answer 3

It's not a full answer I know, but while there are no dead reads, there are locks that can give you some idea what is going on.

In session 1 if you insert a row with primary key 7, then you will not see it when you select from session 2. (That would be a dirty read).

However, if you attempt an 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 will commit or rollback. You can use "WAIT 10" to wait 10 seconds for this to happen.

A similar story exists for updates or anything that would cause a unique constraint violation.