Ora 14402 updating partition

PARTITION_NAME LAST_ANALYZED NUM_ROWS BLOCKS SAMPLE_SIZE HIGH_VALUE PORTAL_SERVICE_1 12/8/2016 41 1 PORTAL_SERVICE_2 6/8/2016 44 2 PORTAL_SERVICE_3 10/8/2016 29602 2014 29602 3 PORTAL_SERVICE_OTHERS 24/5/2016 0 110 DEFAULT i get error: Error report - SQL Error: ORA-14402: updating partition key column would cause a partition change 14402.00000 - "updating partition key column would cause a partition change" i am not allowed to use Enable Row Movement.

ora 14402 updating partition-46

Let’s try and move it by updating the partition key value for that office.

UPDATE CITY_OFFICES SET CITY_ID = '283' WHERE OFFICE_NUMBER = 4; UPDATE CITY_OFFICES SET CITY_ID = '283' WHERE OFFICE_NUMBER = 4 * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change Why didn’t the update work?

Hi, When I am trying to execute an update statement where i am tring to update date values emp_det from 11-oct-2010 to 12-nov-2010.

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.

However, when row movement is enabled, the rows can be physically moved (similar to delete insert) with Answer to your Question: no performance increase if you enable row movement, but you can shrink tables which would increase your performance for full table queries.

ASKTOM also gives a very good answer to your question: https://asktom.oracle.com/pls/asktom/f?

Let’s create a quick partitioned table example and see how moving a row works.

CREATE TABLE CITY_OFFICES 2 ( 3 OFFICE_NUMBER NUMBER NOT NULL, 4 CITY_ID VARCHAR2(12) NOT NULL, 5 OFFICE_NAME VARCHAR2(30) NOT NULL 6 ) 7 PARTITION BY LIST (CITY_ID) 8 ( 9 PARTITION P282 VALUES ('282'), 10 PARTITION P283 VALUES ('283'), 11 PARTITION P284 VALUES ('284')); Table created.

[email protected] ~]$ oerr ora 1440214402, 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.

Tags: , ,