RE: Options to move a large table
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <Thomas.LaPorte@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Sep 2010 18:25:32 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas A. La Porte
Sent: Tuesday, September 14, 2010 2:07 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxx>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
