Enable Row Movement

"Updating partition key column would cause a partition change" is an Oracle error message. Oracle logs it as ORA-14402. This is something you will have to deal when working with Oracle partitioned table while updating existing records. Before diving in with this problem, we will need to understand ROW Movement in Oracle DBMS.  Oracle lets you enable or disable the row movement for Tables and Indexes.

SYNTAX:

ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name ENABLE ROW MOVEMENT;

When you CREATE or ALTER a partitioned table, a row movement clause either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if it's key is updated. The default is DISABLE ROW MOVEMENT. A partitioned table should prevent moving data from one partition to another unless you are correcting data. If the partition key column needs frequent update, then you may re-think about another solid column from a table as a good candidate for partition key column.

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.

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')));

INSERT:

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;
/

UPDATE:

BEGIN
UPDATE baniya.sales SET time_id ='29-MAY-2016' WHERE time_id = TO_DATE('02-APR-2016', 'DD-MON-YYYY');
COMMIT;
END;
/

OUTPUT:

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.

SOLUTION:
If you have read the above explanation, you would know why you are seeing this error? What happened here? When we issued the UPDATE on time_id column, the data from sales_m2_2016 partitions are going to move to partition sales_m1_2016 partition. Row enables are prohibited by default therefore; you get Oracle error ORA-14402.

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:

1. Enable Row Movement
2. Update Data
3. Disable Row Movement

Enable Row Movement:

 ALTER TABLE baniya.sales ENABLE ROW MOVEMENT;

UPDATE:

BEGIN
    UPDATE baniya.sales SET time_id ='29-MAY-2016' WHERE time_id = TO_DATE('02-APR-2016', 'DD-MON-YYYY');
    COMMIT;
END;
/

OUTPUT:
PL/SQL procedure successfully completed.
This clearly tells an update is successful and we didn’t see error like before.

Disable Row Movement:

ALTER TABLE baniya.sales DISABLE ROW MOVEMENT;

We enabled the row movement of sales table, if someone forgot to disable it, you can run a query against dba_tables view to get the status of row movement.

SQL QUERY:

SELECT owner,
     table_name,
     row_movement
FROM dba_tables
WHERE owner = 'BANIYA' AND table_name = 'SALES';

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 feature will not work when row movements are disabled. Let’s see this in action.To demo this, we will delete all the records from sales table, commit the change and then flashback table to what it was 10/15 minutes ago.

DELETE:

BEGIN
      DELETE FROM baniya.sales;
      COMMIT;
END;
/

OUTPUT:

Oracle Error

FLASHBACK TABLE Baniya.sales TO TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' minute)
Error report -
SQL Error: ORA-08189: cannot flashback the table because row movement is not enabled
08189. 00000 -  "cannot flashback the table because row movement is not enabled"
*Cause:    An attempt was made to perform Flashback Table operation on a table for
           which row movement has not been enabled. Because the Flashback Table
           does not preserve the rowids, it is necessary that row
           movement be enabled on the table.
*Action:   Enable row movement on the table

SOLUTION:

ALTER TABLE baniya.sales ENABLE ROW MOVEMENT;
FLASHBACK TABLE Baniya.sales TO TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' minute);
ALTER TABLE baniya.sales DISABLE ROW MOVEMENT;

OUTPUT:

Table BANIYA.SALES altered.
Flashback succeeded.
Table BANIYA.SALES altered.

Now, all the data are back to sales table. If you have followed the examples, you should be able to select data from sales table. Do you see any data on sales table?

BEST PRACTICES:
Row movements should be disabled on partitioned table as soon as you are done updating. If the table needs frequent update on key column, it may cause poor performance to a query running against the table. When the updates are happening, the data moves from one partition to another causing DELETE plus INSERT and re-organizing data along with Index. This shift of data from one partition to another chew too much I/O causing poor performance to a query running against the table.