Re: Partitioning an Existing table in Oracle 9.2.0.7.0

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'))

• 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

pankaj_wolfhunter_at_yahoo.co.in wrote:
> sangu_rao_at_yahoo.co.in wrote:
> > Hi,
> > I want to Partition an existing table which is having millions of
> > calls. The table also has 4 indexes and lot of few constraints. What
> > will be the best approach?
> >
> > I have explored on the ALTER TABLE ------ EXCHANGE option.
> > When i do this i am getting some errors like
> > ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
> >
> > As i mentioned my base table contains few indexes (one of the index is
> > a unique index which has been created during the Primary key
> > constraint) and constraints. I have created the Partitioned table also
> > with the same syntax and with same Index and constraints. But still i
> > am getting the above error.
> >
> > Can anyone please suggest me what could be the best approach to
> > partition a table which contains Millions of rows and lot of indexes
> > and constraints. Does the ALTER TABLE -- EXCHANGE PARTITION INCLUDING
> > INDEXES export all the indexes and constraints along with the data from
> > base table to destination table.
> > How much time this exchange may take approximately if the table is
> > populates with 1 million records.
> >
> > Please provide your suggestion
> >
> > Thanks
> > Rao
>
> You have to make sure that both the tables have same indexes and
> constraints
> when dng partition exchange. Making sure that, there shouldnt be any
> problem.
>
> If it still persist, try dropping indexes/constraints on both sides, do
> partition-exchange and then recreate indexes/constraints
> Ideally, if indexes and constraints appear on both tables, table
> partition should work fine.
> I hope u know that partition-exchange will empty ur source partition.
> Also there is something to look into if ur table has bitmap-indexes.
> I dont know exactly.
>
> Partition-exchange does not physcically transfer data from source to
> destination.
> It merely updates the data-dictionary to reset the pointer.
> There is no undo-redo, so it is much faster.
Received on Tue Jan 09 2007 - 01:23:46 CST