Oracle – updating partition Key, row movement not allowed

database-partitioningoraclepartitioningsql-update

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

Best Answer

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 Topic