DanBrother的部落格

Solution to ORA-14402: updating partition key column would cause a partition change

$ oerr ora 14402
14402, 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.

CREATE SMALLFILE TABLESPACE "P01" LOGGING DATAFILE '/home/oracle/oradata/testdb/P01.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "P02" LOGGING DATAFILE '/home/oracle/oradata/testdb/P02.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "P03" LOGGING DATAFILE '/home/oracle/oradata/testdb/P03.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "P04" LOGGING DATAFILE '/home/oracle/oradata/testdb/P04.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "P05" LOGGING DATAFILE '/home/oracle/oradata/testdb/P05.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

-- Create table
create table HASH_PARTAB_TEST
(
NUM NUMBER(10),
KEYSTRING VARCHAR2(10) PRIMARY KEY
)
partition by hash (KEYSTRING)
(
partition P01 tablespace P01,
partition P02 tablespace P02,
partition P03 tablespace P03,
partition P04 tablespace P04,
partition P05 tablespace P05
);

col table_name format a30
select table_name,row_movement from user_tables where table_name = 'HASH_PARTAB_TEST';
>>
TABLE_NAME ROW_MOVEMENT
------------------------------ ----------------
HASH_PARTAB_TEST DISABLED

declare
v_rand number(1) default 0;
v_class varchar2(2) default null;
begin
for i in 1..500 loop
select trunc(dbms_random.value('1','6')) into v_rand from dual;
case v_rand
when 1 then v_class := 'AA';
when 2 then v_class := 'AB';
when 3 then v_class := 'AC';
when 4 then v_class := 'AD';
when 5 then v_class := 'AE';
end case;

insert into HASH_PARTAB_TEST(num,keystring)
values(i,v_class||trunc(dbms_random.value('11111111','99999999')));
end loop;
commit;
end;
/

alter table HASH_PARTAB_TEST add constraint uk_HASH_PARTAB_TEST
unique (num) using index tablespace indx nologging;

col class format a5
select substr(keystring,1,2) as class,count(*)
from HASH_PARTAB_TEST
group by substr(keystring,1,2);
>>
CLASS COUNT(*)
----- ----------
AA 96
AD 107
AE 123
AC 82
AB 92

SQL> update HASH_PARTAB_TEST set keystring = replace(keystring,'AC','AA') where keystring like 'AC%';

ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

--Solution:
SQL> alter table HASH_PARTAB_TEST enable row movement;

SQL> col table_name format a30
SQL> select table_name,row_movement from user_tables where table_name = 'HASH_PARTAB_TEST';

TABLE_NAME ROW_MOVEMENT
------------------------------ ----------------
HASH_PARTAB_TEST ENABLED

SQL> update HASH_PARTAB_TEST set keystring = replace(keystring,'AC','AA') where keystring
like 'AC%';

82 rows updated.

select substr(keystring,1,2) as class,count(*)
from HASH_PARTAB_TEST
group by substr(keystring,1,2);

CLASS COUNT(*)
----- ----------
AA 178
AD 107
AE 123
AB 92

SQL> alter table HASH_PARTAB_TEST disable row movement;

TABLE_NAME ROW_MOVEMENT
------------------------------ ----------------
HASH_PARTAB_TEST DISABLED