Re: Options to move a large table

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: Thomas.LaPorte@xxxxxxxxxxxxxx
  • Date: Tue, 14 Sep 2010 14:55:14 -0600

Have you considered using the DBMS_REDEFINITION package?

On 9/14/2010 12:06 PM, Thomas A. La Porte wrote:
Greetings, all.

SQL> select banner from v$version;

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - 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
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.


 -- Tom

Thomas A. La Porte, DreamWorks Animation

Other related posts: