Partition Table maintenance-ORA-14402 updates partition keyword Columns

When using a partition table, you must note that the following error may occur when updating the partition field:

SQL> Update dom_real_category_fl set doc_level = 101 Where doc_level = 51;
Update dom_real_category_fl set doc_level = 101 Where doc_level = 51
*
Error at line 1:
ORA-14402: updating partition key column wocould cause a partition change

 

The error is explained as follows:

ORA-14402: updating partition key column wocould cause a partition change
Cause: An Update statement attempted to change the value of a partition key column causing migration of the row to another partition
Action: Do not attempt to update a partition key column or make sure that the new partition key is within the range containing the old partition key.

 

To solve this problem, you can use the following command to change the attributes of a partition table:

SQL> ALTER TABLE dom_real_category_fl enable row movement;

 

Table altered.

 

Changing the partition key value of the Partition Table means that you want to delete the record and insert a new record. This will lead to the movement of the record. The rowid of the record will change and the related indexes need to be maintained.

SQL> Update dom_real_category_fl set doc_level = 101 Where doc_level = 51;

 

57024 rows updated.

Elapsed: 00:00:34. 61