SET TRANSACTION READ TFM

Recently I've been having to log on the production server as SYSTEM (don't ask why). Obviously, this is a risky thing to do, so I have taken to running

set transaction read only
/

at the start of the session. This prevents me accidentally dropping a key table due to a sudden moment of madness (or just because I have lost track of which PuTTY session is logged on to which database).

This morning I was monitoring the status of a job going through the system. It seemed to have been stuck on one stage for over half an hour. I was worried because this stage ought to have taken a few seconds. I checked the V$SESSION_WAIT view and there was loads of activity: a different wait event every time I queried on that session.

Of course, what I had forgotten was that setting the transaction to READ ONLY doesn't just prevent me executing DML or DDL [see update below - APC] in the session: it also sets the isolation level. So (in the words of the documentation) "All subsequent queries in that transaction only see changes committed before the transaction began." No wonder I wasn't seeing any changes in the status table data! Obviously the V$ views, being dynamic views on the X$ tables, have a back door through the isolation level. Which I suppose makes sense but it could confuse a stupid person.

One to file under Oracle Things I Shouldn't Forget.

Update

In his comment Hemant Chitale points out that SET TRANSACTION READ ONLY does not prevent DDL (because DDL issues an implicit commit and so ends the transaction). Actually in real life I really just worry about changing production data; I just used the DROP TABLE example for dramatic effect. But of course, this is no excuse for not writing something wrong in article, especially as the documentation I linked to makes that very point.

As an ironist I can only applaud the name I gave the article.