RE: Moving LOBs of a particular partition to a new tablespace

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 29 Mar 2004 10:47:14 -0600
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B002FD@EXCHMN3>

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_at_lifetouch.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Philip Douglass Sent: Monday, March 29, 2004 10:32 AM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 29 2004 - 10:47:39 CST