ORA-14402: updating partition key column would cause a partition change

Error: ORA-14402: updating partition key column would cause a partition change

Analysis:

Created a Sample test table to check if a row can move across the partition if gets updated

SQL> create table test ( a number) partition by range (a) ( partition amin values less than (2), partition a1  values less than (4) , partition amax values less than (maxvalue));

Table created.

Partition values:

amin -> less than 2
a1    -> 2-3
amax -> greater than equal to 4

SQL>  insert into test values (1);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> insert into test values (3);

1 row created.

SQL> insert into test values (4);

1 row created.

SQL> insert into test values (5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

         A
----------
         1
         2
         3
         4
         5

SQL> select * from test partition (amin);

         A
----------
         1

SQL> select * from test partition (a1);

         A
----------
         2
         3

SQL> select * from test partition (amax);

         A
----------
         4
         5

Update a=2 to a=7 to move it to partition amax


SQL> update test set a=7 where a=2;
update test set a=7 where a=2
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>  update test partition (a1) set a=7 where a=2;
 update test partition (a1) set a=7 where a=2
        *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
SQL> alter table test enable row movement;
Table altered.

SQL>  update test partition (a1) set a=7 where a=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test partition (amin);


         A
----------
         1

SQL> SQL> select * from test partition (a1);


         A
----------
         3a=2 has got updated to a=7 & moved from a1 to amax partition
 
 SQL> select * from test partition (amax);


         A
----------
         4
         5
         7

Similarly a=3 moved to amax partition 
 
SQL>  update test set a=7 where a=3;

1 row updated.

SQL> commit;

Commit complete.
SQL>  select * from test partition (amin);


         A
----------
         1

SQL> select * from test partition (a1);

no rows selected

SQL>  select * from test partition (amax);

         A
----------
         4
         5
         7
         7Solution: Enable ROW Movement