RE: What is the timestamp of a Select from a busy table
Date: Wed, 14 Sep 2016 18:51:00 +0000
Message-ID: <1AFD62082EEAF0448EF1815139687F1369BADBD7_at_NC2PWEX504.us.ad.lfg.com>
When you say re-create the table, I assume you are talking about a second table (perhaps you are re-orging it with partitions or something). If you can’t get the down time, you can use DBMS_REDEF package. It might not be fast, but it will move all the existing rows from the old table into the new one, online. In the background it uses a MV log on the original table (if I remember correctly) to track the changes. Depending on how long the Redef takes, you intermittently can run DBMS_REDEF.SYNC_INTERIM_TABLE to bring the changed rows over. Once the main Redef is done, you run DBMS_REDEF.FINISH_REDEF_TABLE and that performs a final sync and it renames the second table back to the name of the original table. Now, there are a lot of little details you have to consider like triggers, grants, etc. When the final “finish” runs, the tables will be locked to prevent change, but, if you have kept your “syncs” up, it should be very quick.
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:51:00 CEST