RE: Moving LOBs of a particular partition to a new tablespace
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Mar 2004 10:47:14 -0600
Philip
I think I found what you are referring to. Take a look at the following.
I didn't want to post material from asktom, but I could only find this
posting in Google's cache section.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From asktom.oracle.com
Move LOB of partition table August 14, 2003
Reviewer: Braniko from Belgrade, Serbia and Montenegro
Hi,
The above example works fine with nonpartition tables. What if we have
partition
table with LOB column?
After execution
ALTER TABLE PartitionTableName
EXCHANGE PARTITION PartitionName WITH TABLE NonPartitionTableName
WITHOUT VALIDATION;
And
ALTER TABLE PartitionTableName
MOVE PARTITION PartitionName TABLESPACE NewTableSpace
LOB segment and LOB Index segment still exist in tablespace where
nonpartition
table was created.
ALTER TABLE PartitionTableName MOVE LOB (LobColumnName) STORE AS (TABLESPACE
NewTablespace)
Gives error: ORA-14511: cannot perform operation on a partitioned
Followup:
alter table <tname> move partition <pname> lob (<cname>) store as (
tablespace
<tablespace_name> )
you sort of have to specify the partition you want to operate on.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Philip Douglass
Sent: Monday, March 29, 2004 10:32 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Moving LOBs of a particular partition to a new tablespace
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.
----------------------------------------------------------------
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:
