CODEWITHSUNDEEP
oracle-databasesql-updatepartitioningdatabase-partitioning
Ps-kl
Ps-kl

Reputation: 117

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

Upvotes: 3

Views: 33646

Answers (4)

thebiggestlebowski
thebiggestlebowski

Reputation: 2779

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: http://www.dba-oracle.com/t_enable_row_movement.htm

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"

Upvotes: 1

Bhaskar
Bhaskar

Reputation: 169

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

alter table <table_name> enable row movement

Upvotes: 1

jyapx
jyapx

Reputation: 2509

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.

Upvotes: 0

Gio Italy
Gio Italy

Reputation: 131

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

Upvotes: 11

Related Questions