Updating partition key column would cause a partition change

When row moments are disabled and someone is trying to update the partition key column, Oracle will throw ORA-14402 error.If you are just trying to update data once, you will need to enable row movement before updating data and then disable the movement upon committing the updates.This clause either enables or disables the migration of a row to a new partition if it's key is updated. A partitioned table should prevent moving data from one partition to another unless you are correcting data.

updating partition key column would cause a partition change-10

ERROR: ORA-14402 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 reproduce ORA-14402 issue, we will need to create a partitioned table and then try to update the data from partition key column.

Below, we will create a partitioned Table, Insert some records and update the records that we just inserted to replicate the error ORA-14402.

PARTITION TABLE DDL: CREATE TABLE baniya.sales( prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10, 2)) PARTITION BY RANGE(time_id)( PARTITION sales_m1_2016 VALUES LESS THAN(TO_DATE('01-APR-2016', 'dd-MON-yyyy')), PARTITION sales_m2_2016 VALUES LESS THAN(TO_DATE('01-MAY-2016', 'dd-MON-yyyy')), PARTITION sales_m3_2016 VALUES LESS THAN(TO_DATE('01-JUN-2016', 'dd-MON-yyyy'))); BEGIN INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'02-MAY-2016',5,543,22,5000.00); INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'02-APR-2016',5,543,22,5000.00); INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'31-MAY-2016',5,543,22,5000.00); COMMIT; END; / Error starting at line : 32 in command - BEGIN UPDATE baniya.sales SET time_id ='29-MAY-2016' WHERE time_id = TO_DATE('02-APR-2016', 'DD-MON-YYYY'); COMMIT; END; Error report - ORA-14402: updating partition key column would cause a partition change ORA-06512: at line 2 14402.

00000 - "updating partition key column would 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.

Can anybody please suggest any alternative to update the row.

can anybody shed some light if this can be used in the scenario: I came across this question as the top Google result when searching for ORA-14402.create table INVOICE( INVOICE_ID NUMBER , INVOICE_SK NUMBER , INVOICE_AMOUNT NUMBER , INVOICE_TEXT VARCHAR2(4000 Char) , B2B_FLAG NUMBER -- 0 or 1 , ACTIVE NUMBER(1) -- 0 or 1 ) PARTITION BY LIST (ACTIVE) SUBPARTITION BY LIST (B2B_FLAG) ( PARTITION p_active_1 values (1) ( SUBPARTITION sp_b2b_flag_11 VALUES (1) , SUBPARTITION sp_b2b_flag_10 VALUES (0) ) , PARTITION p_active_0 values (0) ( SUBPARTITION sp_b2b_flag_01 VALUES (1) , SUBPARTITION sp_b2b_flag_00 VALUES (0) ) ) For perfomance reasons the table should get a "Composite List-List" partitioning, see The problematic point is, that the ACTIVE-Flag will change requently for a huge amount of records and sometimes also the B2B_FLAG.Oracle throws an error : ORA-14402 updating partition key column would cause a partition change I think that this is because emp_det is a partitioning key of a partitioned table.Oracle documentation says that "UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement" .How to solve this is issues with out row movement and recreate partition.