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@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Chen Zhou
Sent: Wednesday, September 14, 2016 2:05 PM
To: oracle-l@xxxxxxxxxxxxx
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.**
