Updating partition key column would cause a partition change

Will Oracle automatically recognize the records, for which the partitioning value has changed and move them to the appropriate partion or do I have to call some kind of maintenance function, in order to reorganize the partitions?

create table T_TESTPART ( pk number(10), part_key number(10) ) partition by list (part_key) ( partition p01 values (1), partition p02 values (2), partition pdef values (default) ); alter table T_TESTPART add constraint pk_pk primary key (PK); I did not do any performance tests, but Oracle will probably delete the row from the first partition and insert it to the second partition. In my own databases, I usually only use partitioning on columns that do not change.

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.

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

Leave a Reply