read uncommited

[Frank Swarbrick]

In DB2 you can do something like the following:

SELECT *
FROM MYTABLE
WITH UR;

If you have, say, a batch application that is inserting a lot of rows in to
a table, with no commits until the end of the job, doing the above query
while the batch update job is still running will allow you to see the
inserted but not yet committed rows.

Not something that you would want to do with a user application, but it's
something that as a developer I have found occasionally useful (seeing what
my batch job is "doing" before it's actually done).

Is there any such feature available for Oracle?

Thanks,
Frank

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403

[Michel Cadot]

"Frank Swarbrick" <Frank.S...@efirstbank.com> a �crit dans le message de news: 4B0D2ABA.6...@efirstbank.com...

Read uncommitted is impossible in Oracle.
You can set client info using dbms_application_info package to
know where your batch is in another session querying v$session.

Regards
Michel

[Malcolm Dew-Jones]

Frank Swarbrick (Frank.S...@efirstbank.com) wrote:
: In DB2 you can do something like the following:

: SELECT *
: FROM MYTABLE
: WITH UR;

: If you have, say, a batch application that is inserting a lot of rows in to
: a table, with no commits until the end of the job, doing the above query
: while the batch update job is still running will allow you to see the
: inserted but not yet committed rows.

: Not something that you would want to do with a user application, but it's
: something that as a developer I have found occasionally useful (seeing what
: my batch job is "doing" before it's actually done).

: Is there any such feature available for Oracle?

No, Oracle prevents you from seeing data until it's committed.

The way around that for debugging is to use autonomous transactions
(google it). You might, for example, add a trigger that records log
details about the running job in a logging table, so set that trigger to
use autonomous transactions so the log details can be committed and seen.

[Frank Swarbrick]

>>> On 11/25/2009 at 12:42 PM, in message <4b0d96bf$1...@news.victoria.tc.ca>,

Malcolm Dew-Jones<yf...@vtn1.victoria.tc.ca> wrote:
> Frank Swarbrick (Frank.S...@efirstbank.com) wrote:
> : In DB2 you can do something like the following:
>
> : SELECT *
> : FROM MYTABLE
> : WITH UR;
>
> : If you have, say, a batch application that is inserting a lot of rows
> in to
> : a table, with no commits until the end of the job, doing the above
> query
> : while the batch update job is still running will allow you to see the
> : inserted but not yet committed rows.
>
> : Not something that you would want to do with a user application, but
> it's
> : something that as a developer I have found occasionally useful (seeing
> what
> : my batch job is "doing" before it's actually done).
>
> : Is there any such feature available for Oracle?
>
> No, Oracle prevents you from seeing data until it's committed.

I did google a bit and thought that Oracle would not allow it, but wanted to
verify by actually asking the question myself.
I guess I can understand why the decision was made. Not sure I agree with
the reasoning, but... Probably not a big deal in the end.
Thanks!

[Mark D Powell]

On Nov 27, 12:02 pm, "Frank Swarbrick"
<Frank.Swarbr...@efirstbank.com> wrote:
> >>> On 11/25/2009 at 12:42 PM, in message <4b0d96b...@news.victoria.tc.ca>,
> Malcolm Dew-Jones<yf...@vtn1.victoria.tc.ca> wrote:

> P: 303-235-1403- Hide quoted text -
>
> - Show quoted text -

Frank, the Oracle default read consistency model is such that a query
always returns a time consistent set of data, that is, all rows
returned in the query are as the rows existed at the same point in
time. The locking scheme is such that writes do not block readers.
Hence Oracle has no need to allow dirty reads.

HTH -- Mark D Powell --