Thursday, June 28, 2007


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.


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.



Blogger Hemant K Chitale said...

Not sure where you got that information that SET TRANSACTION READ ONLY prevents DML and DDL.

Every DDL includes an implicit commit that is issued _first_ even if the DDL fails.

See :
SQL> select table_name from user_tables;


SQL> set transaction read only;

Transaction set.

SQL> drop table another_table;

Table dropped.

SQL> select table_name from user_tables;



29 June 2007 at 02:19:00 GMT-7  
Anonymous Anonymous said...



11 October 2008 at 10:09:00 GMT-7  
Blogger Jerry said...

cheap wedding gowns
discount bridal gowns
China wedding dresses
discount designer wedding dresses
China wedding online store
plus size wedding dresses
cheap informal wedding dresses
junior bridesmaid dresses
cheap bridesmaid dresses
maternity bridesmaid dresses
discount flower girl gowns
cheap prom dresses
party dresses
evening dresses
mother of the bride dresses
special occasion dresses
cheap quinceanera dresses
hot red wedding dresses

18 June 2009 at 09:21:00 GMT-7  

Post a Comment

<< Home