Updating partition key column would cause a partition change

00000 - "updating partition key column would cause a partition change" i am not allowed to use Enable Row Movement.Can anybody please suggest any alternative to update the row.Many partitioned table examples use regions, cities and states as list examples.

updating partition key column would cause a partition change-63

As far back as Oracle 8.1.5 documentation, row movement applied to updatable partition keys.

A list partition where the partition key is a specific value is a good example of this.

One of the relatively newer features in Oracle concerns the moving of rows. An early use of row movement was highlighted in Oracle8i, and row movement then, as well as now, applied to moving rows in a partitioned table.

Why would a row move and who or what controls that movement? With newer releases of the Oracle RDBMS, where else does row movement come into play, and are there any gotcha's with respect to row movement operations?

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.

If the update isn’t a mistake and you would want to correct your data by doing the update, you will need to perform three things: Beside update, we will need to enable row movement when using FLASHBACK with table.

Oracle Flashback lets you rollback table data to a particular point in time.

This shift of data from one partition to another chew too much I/O causing poor performance to a query running against the table.

Tags: , ,