updating partition Key, row movement not allowed
to work around the error "ORA-14402: updating partition key column would cause a partition change" you can follow these steps
1) Check if for your table is enabled the row_movement
SELECT owner,
table_name,
row_movement
FROM dba_tables
WHERE table_name in ('YOUR_TABLE');
2) If is disabled you can enable movement with this script
alter table YOUR_TABLE enable row movement;
After that you can update the partition key column
Related videos on Youtube
Ps-kl
Updated on September 15, 2022Comments
-
Ps-kl over 1 year
i want to update a partition key. the partition is as below
PARTITION_NAME LAST_ANALYZED NUM_ROWS BLOCKS SAMPLE_SIZE HIGH_VALUE PORTAL_SERVICE_1 12/8/2016 4133 174 4133 1 PORTAL_SERVICE_2 6/8/2016 4474 174 4474 2 PORTAL_SERVICE_3 10/8/2016 29602 2014 29602 3 PORTAL_SERVICE_OTHERS 24/5/2016 0 110 DEFAULT
this partition is applied on column Portal_Service_id. i want to update the value of portal service id from 2 to 1.
when i try
update trans set PORTAL_SERVICE_ID = 1 where ID = 2054;
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.
Can anybody please suggest any alternative to update the row.
can anybody shed some light if this can be used in the scenario:
UPDATE <table_name> PARTITION (<partition_name>) SET <column_name> = <value> WHERE <column_name> <condition> <value>;