oracle分区问题：更改分区键值列的数据，导致ORA-14402错误

wwmb · 发表于 2004-11-1 11:30

请问，有什么风险最小的方法来解决？

xzh2000 · 发表于 2004-11-1 11:38

alter table xxx enable row_movement;

wwmb · 发表于 2004-11-1 12:11

最初由 xzh2000 发布
[B]alter table xxx enable row movement; [/B]

刚才试验了，解决了。但是不知道这个是什么原理？对分区表有什么影响。

foreverlee · 发表于 2004-12-23 15:03

顶有人知道么？如何解决ORA-14402

foreverlee · 发表于 2004-12-23 15:04

Question: Why am I getting an ora-14402 error when I update a partition key
Answer: You cannot update the value of the partition key, the only way you can go about this is by deleting the old row and adding a new row to the table

难道只能重新insert?

foreverlee · 发表于 2004-12-23 15:08

最初由 xzh2000 发布
[B]alter table xxx enable row_movement; [/B]

ok 问题解决了

from asktom:

It is possible to update a partition key in all releases -- what is not possible
to do prior to 8i is to update the partition key in such a fashion as to cause
it to move from partition to partition.  For example:

ops$tkyte@8.0> CREATE TABLE partitioned
  2  ( x int,
  3 y int,
  4 z DATE
  5  )
  6  PARTITION BY RANGE (z)
  7  (
  8  PARTITION part_1 VALUES LESS
         THAN(to_date('01-jan-1995','dd-mon-yyyy')),
  9  PARTITION part_2 VALUES LESS
         THAN(to_date('01-jan-1996','dd-mon-yyyy'))
10  )
11  /

Table created.

ops$tkyte@8.0>
ops$tkyte@8.0> insert into partitioned values
  2  ( 1, 1, to_date('01-jan-1994','dd-mon-yyyy') );

1 row created.

ops$tkyte@8.0>
ops$tkyte@8.0> insert into partitioned values
  2  ( 2, 1, to_date('01-mar-1995','dd-mon-yyyy') );

1 row created.

ops$tkyte@8.0>
ops$tkyte@8.0> commit;

Commit complete.

ops$tkyte@8.0>
ops$tkyte@8.0> update partitioned set
  2          z = to_date('01-jan-1993')
            where z = to_date('01-jan-1994')
  3  /

1 row updated.

That shows we CAN update a partition key in 8.0

ops$tkyte@8.0> rollback;

Rollback complete.

ops$tkyte@8.0>
ops$tkyte@8.0> update partitioned set
  2  z = decode( x, 1, to_date('01-mar-1995','dd-mon-yyyy'),
  3             2, to_date('01-jan-1994','dd-mon-yyyy') )
  4  /
update partitioned set
   *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

But we cannot have it migrate.  Now, in Oracle8i, release 8.1 we can:

ops$tkyte@8i> CREATE TABLE partitioned
  2  ( x int,
  3 y int,
  4 z DATE
  5  )
  6  PARTITION BY RANGE (z)
  7  (
  8  PARTITION part_1 VALUES LESS
      THAN(to_date('01-jan-1995','dd-mon-yyyy')),
  9  PARTITION part_2 VALUES LESS
      THAN(to_date('01-jan-1996','dd-mon-yyyy'))
10  )
11  ENABLE ROW MOVEMENT
12  /

Table created.

Enable row movement will allow us to update a partition key and have it move
from partition to partition

ops$tkyte@8i>
ops$tkyte@8i> insert into partitioned values
  2  ( 1, 1, to_date('01-jan-1994','dd-mon-yyyy') );

1 row created.

ops$tkyte@8i>
ops$tkyte@8i> insert into partitioned values
  2  ( 2, 1, to_date('01-mar-1995','dd-mon-yyyy') );

1 row created.

ops$tkyte@8i>
ops$tkyte@8i> commit;

Commit complete.

ops$tkyte@8i>
ops$tkyte@8i> select rowid, a.* from partitioned a;

ROWID                      X       Y Z
------------------ ---------- ---------- ---------
AAAWBlAADAAAIfKAAA       1       1 01-JAN-94
AAAWBmAADAAAIgKAAA       2       1 01-MAR-95

ops$tkyte@8i> update partitioned set
  2  z = decode( x, 1, to_date('01-mar-1995','dd-mon-yyyy'),
  3             2, to_date('01-jan-1994','dd-mon-yyyy') )
  4  /

2 rows updated.

ops$tkyte@8i> select rowid, a.* from partitioned a;

ROWID                      X       Y Z
------------------ ---------- ---------- ---------
AAAWBlAADAAAIfKAAB       2       1 01-JAN-94
AAAWBmAADAAAIgKAAB       1       1 01-MAR-95

ops$tkyte@8i> commit;

Commit complete.

but -- notice the side effect -- the rows ROWID changed, one of 2 cases in
Oracle8i release 8.1 whereby for the first time a rowid of a row will change
(index organized tables are the other case -- if you update the primary key)

foreverlee · 发表于 2004-12-23 15:11

对请问tom说的最后两句是什么意思?副作用怎么解释？

but -- notice the side effect -- the rows ROWID changed, one of 2 cases in
Oracle8i release 8.1 whereby for the first time a rowid of a row will change

foreverlee · 发表于 2004-12-23 15:13

the rowid changes - it is just like delete + insert
--- from tom. hehe

foreverlee · 发表于 2004-12-23 15:16

I came to this page after getting the "ORA-14402: updating partition key column
would cause a partition change" error message.  I then tried ALTER TABLE xYz
ENABLE ROW MOVEMENT and, like a miracle, my problems fizzled away!  I learnt a
new thing too after 15 years of using oracle.  Thanks Tom.

呵呵夸张了点.
总觉得解决一个问题后很兴奋.

yangtingkun · 发表于 2004-12-23 15:46

rowid发生变化一般问题不大。但是需要注意几点，如果你在程序中保存了rowid，并把这个rowid用于查询，那么rowid的变化则会是一个问题。另外，在使用logminer时，rowid的变化可能会导致使用logminer的undo信息无法还原数据。
至于分区表的enable row movement会不会影响到logminer的使用还不敢确定。