Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

oracle select from table & update (lock)

Go to solution
Petr Simik_1
Valued Contributor

oracle select from table & update (lock)

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
Honored Contributor

Re: oracle select from table & update (lock)


this question relates to the isolation level of a database transaction. Additional Info in the net, i.e. here:

Hope this helps

Petr Simik_1
Valued Contributor

Re: oracle select from table & update (lock)

Thank you. It's long reading but it has my information.
Sanjay Kumar Suri
Honored Contributor

Re: oracle select from table & update (lock)

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".

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.