How to read UNCOMMITED data in Oracle?

[oracle70]

Hi,

I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the user commits. But in Informix there is this thing called ISOLATION LEVELS. For example by setting the ISOLATION LEVEL to DIRTY READ, a user will read dirty data, i.e. the last uncommited updated value of a field by some other user. Is this possible in Oracle by setting some parameter, say in the INIT file?

Also WHAT IS THE DEFAULT LOCKING BEHAVIOUR IN ORACLE? I mean if I want Oracle to automatically issue a READ LOCK (so that nobody can update a record, but view only) everytime a table (or row) is read, and for this to be made effective for the ENTIER DATABASE, how can we achive this? Is there a parameter to change in some INIT file???

Thanks & Regards,
Francis

 

[sem]

AFAIK Oracle supports only READ COMMITTED and SERIALIZABLE isolation levels. By default if you only select form table, you obtain shared lock. You may lock table by

LOCK TABLE <table> IN
[ROW] SHARE|[[SHARE] ROW] EXCLUSIVE|SHARE UPDATE
MODE

You may open whole database as read only or ALTER TABLESPACE READ ONLY. As for locking on read, I suppose that SELECT FOR UPDATE is the only way.



Regards, Dima

 

[oracle70]

Hi Dima,

Thanks so much for your vey prompt reply: Just some clarifications.

1.) READ COMMITTED is pretty obvious: This means that you can read ONLY commited infor of OTHER users. But what does SERIALIZATBLE mean?
2.) You say that, by default if you ONLY select from a table, you obtain a SHARED LOCK. But I thought that a SHARED LOCK was one using which others can alos read BUT CANNOT UPDATE. But in Oracle it is not so. In Oracle others can read and update also?

Thanks & Regards,
Francis

 

[sem]

In Oracle you can not prohibit to view data at all: exclusive lock prevents from updates, shared - from DDL operations.

Regards, Dima