updating partition Key, row movement not allowed

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

4 Answers

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

Enable row movement with this syntex , it will solve the error :

alter table <table_name> enable row movement

Most of the other answers have pointed out one part of the solution, which is:

alter table <table_name> enable row movement 

However, there's more to it than this. Enabling row movement has some tradeoffs, which are pointed out here:

In my case, this led me to double check why we were using a partition in the first place. Ideally, a partition is for data that won't move around once it's in the partition. If you're modifying the partition field (in my case a timestamp) then it may mean you shouldn't be using a partition. If you're sure you want a partition on a mutable field, then at least make sure you periodically run:

"alter table shrink compact" 

I came across this question as the top Google result when searching for ORA-14402. In my particular case I:

1. exported the rows as insert statements
2. updated the index key field and updated the ID field
3. inserted the rows as new rows.

You could then potentially delete the old rows if you have the need or leave them in the old partition.