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.'