Ora 14402 ошибка

Из описания ошибки:

$ oerr ora 14402

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.

следует, что изменение ключа секционирования приведёт к изменению секции. То есть, физическому перемещению записи(ей) из одной секции в другую, что по умолчанию не допускается. Проверьте эти измнения на соответствие бизнес логике.

create table ptab1 (id number, created date)
partition by range (created) interval (numtoyminterval (1,'year')) (
    partition part2019 values less than (date'2020-01-01'),
    partition part2020 values less than (date'2021-01-01'));

insert into ptab1 
    select 1, date'2019-06-30' from dual union all  
    select 2, date'2020-04-30' from dual;  

update ptab1 set created=date'2020-01-30' where id=1;

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

Если есть уверенность, что так и задумано, то включите для таблицы перемещение записей:

select table_name, row_movement
from user_tables where table_name = upper ('ptab1');

TABLE_NAME       ROW_MOVEMENT    
---------------- ----------------
PTAB1            DISABLED        

alter table ptab1 enable row movement;

Table PTAB1 altered.

update ptab1 set created=date'2020-01-30' where id=1;

1 row updated.

«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.

While updating a row in partitioned table, got below error.

SQL> update RANGE_TAB set TIME_LINE=sysdate-2 where QUANT=100;
update RANGE_TAB set TIME_LINE=sysdate-2 where QUANT=100
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

Solution:

This error occurs, if we are trying to update a column( which is partitioned key), where the new value is outside the range of the existing partition, then it will fail.

To fix it enable row movement.

SQL> alter table RANGE_TAB enable row movement;

Table altered.

SQL> update RANGE_TAB set TIME_LINE=sysdate-2 where QUANT=100;

3 rows updated.

The error

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

occurs when you try to update the value of a partition key column.
This will lead to an implicit INSERT operation.
The ROWID of the row was assigned when the row was first inserted, and it doesn’t change. Therefore, by default, Oracle will reject any DML that will cause a rowid to be changed.

Example:

update sales.quarterly_sales
set sold_month = '2016-02'
WHERE unique_sales_id = '3d6fb1ad-243d-4435-97d8-9ca4bfde3ab5';

Since the partition key is the column «sold_month», it will return ORA-14402.

The workaround is to allow row movement for the table:

alter table sales.quarterly_sales enable row movement;

You can now update the row.

You may want to revoke the permission for the rows in the table to change rowid when you are finished:

alter table sales.quarterly_sales disable row movement;

Further reading:
Doc ID 1518567.1 «FAQ: Row Movement Common Questions and Problems on Partitioned Tables»
Doc ID 236191.1: «Updating Partition Key Column Fails with ORA-14402»