Re: Partitioning an Existing table in Oracle 9.2.0.7.0

sangu_rao_at_yahoo.co.in schrieb:
> Hi,
> Thanks for the reply. I have created the partitioned table exactly the
> same way as source table with all indexes. Still the exchange is not
> happening with the indexes. I am giving the example code which i have
> tried.
>
> --- Source table
> CREATE TABLE TEST(A INT PRIMARY KEY,B VARCHAR2(10),C DATE);
>
> INSERT INTO TEST VALUES(1,'2004',TO_DATE('01-10-2003','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(2,'2005',TO_DATE('01-10-2004','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(3,'2006',TO_DATE('01-10-2005','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(4,'2007',TO_DATE('01-10-2006','DD-MM-YYYY'))
> COMMIT
>
> -- Partitioned table
>
> CREATE TABLE TEST2 (A INT PRIMARY KEY,B VARCHAR2(10),C DATE) PARTITION
> BY RANGE (C) (PARTITION PART2004 VALUES LESS THAN (MAXVALUE));
>
> -- Then tried to exchange
>
> ALTER TABLE TEST2 EXCHANGE PARTITION PART2004 WITH TABLE TEST WITHOUT
> VALIDATION.
>
> -- The statement works and i am able to query the table test2. But when
> do any Inserts it is throwing the error " index or partition of such
> index is in unusable state"
> The after rebulding the index it is working. But as i said my actual
> table contains millions of rows and lot of indexes this rebuilt will
> take long time. When i tried with the option Including the indexes it
> is not working i.e.
>
> ALTER TABLE TEST2 EXCHANGE PARTITION PART2004 WITH TABLE TEST INCLUDING
> INDEXES WITHOUT VALIDATION.
>
> -- The above statement is not working it is throwing the error
>
> ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
>
> Why so.Eventhough I have the same index and table structure for both
> source and partitioned table.
>
> Could you please suggest me what could be the best way to EXCHANGE the
> data and the indexes and constraits from source table to partitioned
> table without rebuild.
>
> Thanks
> Rao
>
>
>
>
>

Sorry, in my previous post i just overlooked, you are on the 9.2.0.7, so the documentation links for your version are http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96520/parpart.htm#102660 and
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#20590 The clause to look up is "UPDATE GLOBAL INDEXES" in your version.

Best regards

Maxim Received on Tue Jan 09 2007 - 02:17:48 CST