Re: Can I add two lob column to two hash partitions?

I haven't checked this, but I think your problem is that you are trying to put a 'partition by' clause in the alter table. If you simple add the column it is a column of a partitioned table, and the LOB segment will automatically be equi-partitioned with the table.

You find that you cannot even specify where the partitions have to go (this may be version dependent) when you add the column - instead you have may to add the column then move the resulting lob partitions.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Ed Wong wrote in message ...


>I have a 100GB table with one blob column initally.

>id         integer

>name       varchar2(100)

>output     blob

>

>The table is hash partition by id into 4 different tablespaces(ts1 -

>ts4).  The individual "output" blob column is also hash partition by

>id into 4 different tablespaces(ts_output1 - ts_output4).  Now, I want

>to add a "textoutput" clob column and like to have the same hash

>partition by id so that the clob split into 4

>tablespaces(ts_textoutput1 - ts_textoutput4).

>

>My first question is does it make sense?

>

>Second question is I got the following error message:

>

>SQL> alter table outputresult add (textoutput clob)

>lob(textoutput) store as lob_outputresult_textoutput

>( storage (initial 100m next 100m minextents 1

>maxextents unlimited pctincrease 0 freelists 1 freelist groups 1)

>chunk 8k pctversion 20 nocache disable storage in row)

>partition by hash(id)

>(partition outputresult_textoutput_p1 tablespace ts_textoutput1,

> partition outputresult_textoutput_p2 tablespace ts_textoutput2,

> partition outputresult_textoutput_p3 tablespace ts_textoutput3,

> partition outputresult_textoutput_p4 tablespace ts_textoutput4)

>/

>partition by hash(id)

>*

>ERROR at line 6:

>ORA-01735: invalid ALTER TABLE option

>

>Please help.  Thanks in advance.