Wednesday, August 18, 2010

How to resolve "ORA-14402: updating partition key column would cause a partition change"

SQL> create table part_test
2 (id number, doc varchar2(30))
3 partition by range (id)
4 (partition p1 values less than (100),
5 partition p2 values less than (maxvalue));

Table created.

SQL> insert into part_test values(1,'donghua');

1 row created.

SQL> commit;

Commit complete.

SQL> update part_test set id=id+100;
update part_test set id=id+100
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> alter table part_test enable row movement;

Table altered.

SQL> update part_test set id=id+100;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table part_test disable row movement;

Table altered.