Re: Partitioning an Existing table in Oracle 9.2.0.7.0

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 Mon Jan 08 2007 - 11:09:38 CST