Re: Options to move a large table
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Thomas.LaPorte@xxxxxxxxxxxxxx
- Date: Tue, 14 Sep 2010 13:13:46 -0500
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:
