Re: Moving LOBs of a particular partition to a new tablespace
- From: "Philip Douglass" <philip.douglass@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Mar 2004 11:31:38 -0500
That sure seems like a bug to me. Anyway, if I recall, Tom Kyte had a
workaround that went something like this:
alter table mytable add newcol clob(newcol) store as (tablespace newts);
update table mytable set newcol = oldcol;
(my syntax memory is failing, bear with me)
alter table mytable unused column oldcol;
alter table mytable rename column newcol to oldcol;
alter table mytable drop unused columns;
I'd give you a link to the post, but I forgot to keep it with my notes. You
might be able to find it if you search for it on his site.
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 29, 2004 10:47 AM
Subject: Moving LOBs of a particular partition to a new tablespace
Hi people,
One of my colleagues is having a problem moving LOBs to a new tablespace.
He's trying to move JUST the LOBs in a particular partition of a table to a
new tablespace, WITHOUT affecting the entire partition.
The following syntax:
alter table t1 move partition p1 to ts_p1;
Will move the partition contents to ts_p1 but not the LOB segments
associated with that partition.
So, then he tried:
alter table <table> move partition <Partition> lob(<LOB column>) store as
(tablespace <new TS>);
which works, but, in addition to moving the LOB segments to the appropriate
tablespace, also moves the partition in place in it's own tablespace. Due
to the size of the partition, this considerably increases the time required
for the operation to complete, not to mention the invalidation of all
requisite indexes, and the additional space required for the partition move
to be successful
Does anyone have any ideas or suggestions as to how to move ONLY the LOB
segments?
Thanks,
-Mark
PS This was posted on MetaLink a week ago, and has been met with a
resounding silence....;-)
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
