Re: Swapping partitions

Kenny,

Slight correction:

The clause "[ INVALIDATE | UPDATE ] GLOBAL INDEXES" clause on the partition management variations of the ALTER TABLE command provides some flexibility.

Introduced with Oracle9i, the default is INVALIDATE GLOBAL INDEXES, which was the (unavoidable) behavior in Oracle8 and Oracle8i.

Using the UPDATE GLOBAL INDEXES clause causes the ALTER TABLE partition maintenance operation (i.e. [ EXCHANGE | MOVE | SPLIT | MERGE | TRUNCATE | DROP ] PARTITION, etc) to transactionally maintain the affected entries in the global index. While this makes the ALTER TABLE command complete much more slowly, it also avoids the need for a complete rebuild of the global index, something that become infeasible as things get larger and larger...

Hope this helps...

-Tim

on 12/2/04 8:04 AM, Ken Payton at ken.payton_at_choicepointprg.net wrote:

> You can use the partition exchange clause if you are not using global
> indexes. If you are using global indexes you would need to rebuild
> them.
>
> precreate empty exact matching table, with indexes.
>
> alter table exchange partition partition_name with table table_name
> including indexes without validation;
>
> You could then use the same command to exchange the resulting table into
> a partitioned archiving table.
>
> Kenny

--
http://www.freelists.org/webpage/oracle-l