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

最初由 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)