Or two offices in the same city (from different groups or business units within the same company) merge into one location?
You could split the default partition and add the new location name.
The error message doesnt come right out and say it, but the reason is that row movement is not enabled. The reason why you would know this is due to the fact that row movement by default is not enabled.
A simple alter table statement remedies the update problem. Well, tables are partitioned for a reason, and to help ensure data goes where it is supposed to, you should disallow inadvertent updates or row movements.
Lets 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 didnt the update work?
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.
00000 - "updating partition key column would cause a partition change" i am not allowed to use Enable Row Movement.p=1:0:::: P11_QUESTION_ID:35203106066718 Regarding Shrink (which requires ROW MOVEMENT): I would suggest benchmarking -- collect performance metrics about the table before and after performing the operation.You would expect full scans to operate more efficiently after, you would expect index range scans to either be unchanged or "better" as you have more rows per block packed together (less data spread)[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.PROBLEM: update XXUSER.abc_table set id=102 where a_id=930000000428682 Error at line 6 ORA-14402: updating partition key column would cause a partition change SOLUTION: new value is outside the range of the existing partition when updated partition table. Oracle documentation says that "UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement" .alter table t enable row movement; I did not understand what is meant by "enable row movement".Can anybody please suggest any alternative to update the row.One of the relatively newer features in Oracle concerns the moving of rows.How would you move records from the old partitioned into the new one?Short of deleting from one partition and inserting same into a new one, wouldnt it be easier to be able to perform a single update?