Ora 14402 updating partition key column

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: Beside update, we will need to enable row movement when using FLASHBACK with table.In the example below, a row has to move from the 'SG' Partition to the 'IN' Partition.This is disallowed by Oracle until and unless the DBA ENABLEs ROW MOVEMENT : create table my_emp_tbl 2 (emp_id number not null primary key, 3 country_id varchar2(2), 4 emp_name varchar2(50), 5 join_date date) 6 partition by list (country_id) 7 ( 8 partition my_emp_tbl_in values ('IN'), 9 partition my_emp_tbl_sg values ('SG'), 10 partition my_emp_tbl_us values ('US') 11 ) 12 /Table created.DELETE: 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.

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.SQL select partition_name, num_rows 2 from user_tab_partitions 3 where table_name = 'MY_EMP_TBL' 4 order by partition_position 5 /PARTITION_NAME NUM_ROWS------------------------------ ----------MY_EMP_TBL_IN 33MY_EMP_TBL_SG 33MY_EMP_TBL_US 33SQL select * from my_emp_tbl where emp_id = 4; EMP_ID CO EMP_NAME---------- -- --------------------------------------------------JOIN_DATE--------- 4 SG 9WSXMYH66V6I8B1LKKW7YRTG2VYQGY2OPIT54OFW16-JUL-10SQL update my_emp_tbl set country_id = 'IN' where emp_id = 4;update my_emp_tbl set country_id = 'IN' where emp_id = 4 *ERROR at line 1: ORA-14402: updating partition key column would cause a partition change SQLSo it is not just ALTER TABLE SHRINK that the ENABLE ROW MOVEMENT is necessary for.Yet, it is likely a defect in your design if you have frequent movements of rows between Partitions.Example CREATE TABLE T2( TRANS_ID NUMBER, PARTITION_ID NUMBER) PARTITION BY RANGE(PARTITION_ID) –interval (1) store IN (USERS)( PARTITION P_TRANS_BATCH_ID01 values less than (1001), PARTITION P_TRANS_BATCH_ID02 values less than (1002), PARTITION P_TRANS_BATCH_ID03 values less than (1003)) TABLESPACE USERS; INSERT INTO T2 VALUES(1,1000); INSERT INTO T2 VALUES(1,1001); — ok UPDATE T2SET PARTITION_ID = 1002WHERE PARTITION_ID = 1001; Solution ALTER TABLE T2 ENABLE ROW MOVEMENT; Error starting at line : 20 in command –ALTER TABLE INS. BT_GHI_UPLOAD_TAB_1; Error report –SQL Error: ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations14047.Since the ALTER TABLE SHRINK command appeared and "ENABLE ROW MOVEMENT" has been presented as a requirement, some DBAs have been confused about what it means to enable row movement.

Ora 14402 updating partition key column