RE: What is the timestamp of a Select from a busy table
Date: Wed, 14 Sep 2016 18:10:18 +0000
Message-ID: <1AFD62082EEAF0448EF1815139687F1369BADB93_at_NC2PWEX504.us.ad.lfg.com>
I would say that it selects data based on the most recent commit before the SCN time at which your SELECT is executed. You will never see data that is not committed or committed after your SELECT is initiated.
Chris..
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group cruel_at_lfg.com<mailto:cruel_at_lfg.com> * Desk:317.759.2172 * Cell 317.523.8482
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chen Zhou
Sent: Wednesday, September 14, 2016 2:05 PM
To: oracle-l_at_freelists.org
Subject: What is the timestamp of a Select from a busy table
Hi, Everyone,
I have a general question (or a few ). When I do a select from a table that is actively getting more data inserted, does Oracle just select from the data at that moment when the select command is issued?
New data that get inserted after Oracle started the select work will not be included, right? Is there an accurate way to tell the timestamp/SCN that SELECT is fixed on?
I have a large table (close to 400G with 160million rows), I would like to recreate that table with only the most recent data. Even the most recent data will be many millions of rows.
This obviously will take hours, if not days. During these hours/days, the original table will keep on growng. There is no timestamp column on this table to tell a old record from new one. I can only get the record creation time from another table through foreign key.
So if I can do "insert into newtable (select * from oldtable where some column values in (select column from table3 where timestamp> sysdate-x))", then after that completes, schedule a downtime to stop the old table from being written into, then another "insert into newtable (select * from oldtable where some column values in (select column from table3 where timestamp> last select timestamp)", it will have all new data.
But what is that timestamp/scn for that first select statement? Or is there a better way to get a new table created with only the new data.
Thanks in advance!
Chen
Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 14 2016 - 20:10:18 CEST