RE: Options to move a large table

  • From: Upendra N <nupendra@xxxxxxxxxxx>
  • To: <andrew.kerber@xxxxxxxxx>, <thomas.laporte@xxxxxxxxxxxxxx>
  • Date: Tue, 14 Sep 2010 15:47:19 -0400

You have a few options, here are some:
1. MVs: precreate the tables and create a Materialized View using pre-built 
option with fast refresh. Initial refresh will be painful, once it is done you 
can find a downtime window and drop/rename the tables. You should be able to 
pre-create the indexes ahead of time. Very easy to setup.

2. Oracle Streams: You can setup Streams replication of this table to another 
table. Fairly easy since you only need 1 table to be replicated.

3. If you have licensing you could look into GoldenGate.

-Upendra



Date: Tue, 14 Sep 2010 13:13:46 -0500
Subject: Re: Options to move a large table
From: andrew.kerber@xxxxxxxxx
To: Thomas.LaPorte@xxxxxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx

alter table move, but the performance is probably about the same as a ctas.  On 
the other hand, the move will keep constraints, etc.

On Tue, Sep 14, 2010 at 1:06 PM, Thomas A. La Porte 
<Thomas.LaPorte@xxxxxxxxxxxxxx> wrote:

Greetings, all.



SQL> select banner from v$version;



BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production





I have a large (150GB) two-column table as follows:



SQL> desc takemgr.tmgr_file_metadata;

 Name                             Null?    Type

 -------------------------------- -------- ----------------------

 ID                               NOT NULL CHAR(36)

 METADATA                         NOT NULL BLOB





This table partially resides in a datafile that is reporting corrupted blocks. 
None of the corrupted blocks *appear* to belong to any object (though I'm 
willing to believe that I've done my analysis incorrectly).




I am trying to determine the optimal way to move this table to another 
tablespace, as a part of moving the objects that have extents in this datafile 
prior to dropping the datafile. My primary objective is to minimize the amount 
of time the application needs to be offline.




In a cloned environment where I have done some initial testing I have tried the 
following:



1) Using CTAS, created a copy of the table took more than 24

   hours.

2) Created a hash-partitioned table with same structure and one

   partition; did an "EXCHANGE PARTITION" and then attempted to

   increase the number of partitions using a different

   tablespace. I was hoping that this would allow me to continue

   to use the table while the move happened, but it did not.

   Inserts on the table were blocked while the add partition

   operation was underway (which took multiple hours).



This is primarily a logging table, so it is *almost* exclusively insert. I've 
considered creating a new, empty table in a new tablespace, and then inserting 
the rows from the old table into the new one. I'm just wondering if there is an 
alternative option that I haven't considered.




Thanks!



 -- Tom



Thomas A. La Porte, DreamWorks Animation

<mailto:tlaporte@xxxxxxxxxxxxxxxxxxx>

--

//www.freelists.org/webpage/oracle-l







-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
                                          

Other related posts: