Re: Options to move a large table

From: David Roberts <big.dave.roberts_at_googlemail.com>
Date: Wed, 15 Sep 2010 13:58:19 +0100
Message-ID: <AANLkTinCM6d_fakQ97Em3q-JhkWkYRbWoM7FX3cY7NbD_at_mail.gmail.com>

While I don't have access to a system that I can currently test on, I believe that as long as there are no updates on the data in the table (only selects and inserts), something like the following should work:

rename current_table to old_table;

create new_table with same structure as old_table;

create view current_table as select from old_table union all select from new_table;

You will also need to create an instead of trigger that will convert inserts on the new view into inserts on the new_table.

Obviously this will have impact on some of the performance characteristics of the table, however, the movement of the old records to the new table could be performed at your leisure.

Dave

On Tue, Sep 14, 2010 at 11:25 PM, Goulet, Richard < Richard.Goulet_at_parexel.com> wrote:

> Thomas,
>
> I'll give you two options that I've used successfully on tables
> that were about half the size with few problems.
>
> 1) Alter table <table_name> move online tablespace
> <new_tablespace>;
>
> 2) rename <table_name> to <old_table_name>;
> Create <table_name> tablespace <new_tablespace> as select *
> from <old_table_name> where rownum < 1;
> copy append <table_name> using select * from <old_table_name>;
>
> All of the above will let tyou do what you need with little trouble and
> keep the app online. And in these cases you really don't care how long
> it takes.
>
>
> Dick Goulet
> Senior Oracle DBA/NA Team Lead
> PAREXEL International
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas A. La Porte
> Sent: Tuesday, September 14, 2010 2:07 PM
> To: oracle-l_at_freelists.org
> Subject: Options to move a large table
>
> 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_at_anim.dreamworks.com>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Wed Sep 15 2010 - 07:58:19 CDT