Unanswered: how to select data while updation is in progress
I have a huge table, which is taking 3 hrs to update all records. In the mean time i want to select the data from the same table. Is there any possible way to do this. Cany anyone help me in finding a solution.
Thanks & Regards
What "problem" are you trying to solve? In Oracle, writers do not block readers, so it is possible to select data from updated rows. Additionally, the resultset will be in consistent state from the time when SELECT was called (if it takes long time, it does not reflect updates which happened during its run). Keep in mind, that any UPDATE results are visible to other sessions after its transaction commit.
You may find more information in Oracle Concepts. It is part of the documentation, found e.g. online on http://tahiti.oracle.com/.
I couldn't agree more, *unless* the one who wrote the UPDATE script got the idea of "committing after every 1000 records are being updated". If so, well, it was most probably a bad idea and promises unexpected errors sooner or later (such as ORA-01555, for example).
Thank you both for the quick reply. But, i would like to find out the impact of updates on select statement. If the table locks are enable during updates, how the data effected with the select query. Please give me some more inputs.
Unless the person who wrote the UPDATE statement explicitely requested an exclusive table lock, there won't be any locks that will prevent the SELECT from executing.
Not true. Even exclusive table lock does not prevent from selecting table data, as may be easily checked.
SQL> lock table tt1 in exclusive mode;
SQL> select * from tt1;
This feature is deeply described in Oracle Concepts book, Part III Oracle Database Features, Chapter 13 Data Concurrency and Consistency; even with the mechanisms for achieving the resultset consistency. As it is quite large and easily available on the page I posted, I do not see any reason to copy it here.