oracle表分区：更改分区键值列的数据，导致ORA-14402错误
alter table xxx enable row movement;
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)
