CODEWITHSUNDEEP
oracle-databaseisolation-level
JosephStyons
JosephStyons

Reputation: 58795

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

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?

Upvotes: 1

Views: 490

Answers (3)

WW.
WW.

Reputation: 24311

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.

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 2

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

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

Upvotes: 0

Related Questions