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.

4 comments:

  1. Thanks Donghua,

    still actual solution in 2020 :)

    ReplyDelete
  2. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post. 増田裕介

    ReplyDelete
  3. Tried running this through Oracle data Integrator but got the below error
    ORA-01735: invalid ALTER TABLE option

    Please Help

    ReplyDelete