oracle select from table & update (lock)

[Petr Simik_1]

Hello,
I am looking for this (I hope) simple information:
When I run select * from long_table (takes a minutes before giving output) and during this time somebody update/insert last row of this table. Is this change shown at my output ??

In other words: Is select in specific time real snapshot of table ? Or it is getting row by row with its current data ?


Purpose of know this is:
I want to compare two tables within two databases on different sites. Tables are still under changes (by customers). 1st DB is master and 2nd is copy. I want to find discrepances done by imperfect synchronisation (relative updates). My idea is to launch select in specific time on both databases in low-peak period and than compare and find differences.

Do you know how does Oracle /select works or ideas with comparation?

[Volker Borowski · Accepted solution]

Hi,

this question relates to the isolation level of a database transaction. Additional Info in the net, i.e. here:
http://www.cise.ufl.edu/help/database/oracle-docs/server.920/a96524/c21cnsis.htm

Hope this helps
Volker

[Petr Simik_1]

Thank you. It's long reading but it has my information.

[Sanjay Kumar Suri]

Oracle implements consistency to guarantee that the data seen by a statement or transaction does not change until that statement or transaction completes.

Oracle always use statement-level consistency, which ensures that the data visible to a statement does not change during the life of that statement.

Oracle never user locks for reading operations, since reading operations will never block writing operations. Instead rollback segments are used to re-create the image needed.

Rollback segments are released for reuse when the transaction writing to them commits.

Sometimes a consistent image can't be created. In this case, Oracle give exception "snapshot too old" or "can't serialize access for this transaction".

sks