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

oracle@ebs ~]$ 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.

solution:
alter table xxx enable row movement;

 

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

practice:
version: 9i

SQL> create table partitioned (x int, y int, z date)
  2  partition by range(z)
  3  (partition part_1 values less than (to_date('2008-01-01','yyyy-mm-dd')),
  4  partition part_2 values less than (to_date('2009-01-01','yyyy-mm-dd')));

Table created

SQL> insert into partitioned values(1, 1, to_date('2007-09-01','yyyy-mm-dd'));

1 row inserted

SQL> commit;

Commit complete

SQL> insert into partitioned values(2, 1, to_date('2008-09-01','yyyy-mm-dd'));

1 row inserted

SQL> commit;

Commit complete

SQL> select rowid, a.* from partitioned a;

ROWID                                                    X                                       Y Z
------------------ --------------------------------------- --------------------------------------- -----------
AADl6vAAEAAAmVNAAA                                       1                                       1 2007-9-1
AADl6wAAEAAAmVVAAA                                       2                                       1 2008-9-1

 

SQL> update partitioned set z=to_date('2006-09-01','yyyy-mm-dd') where z=to_date('2007-09-01','yyyy-mm-dd');

1 row updated

--That shows we CAN update a partition key

SQL> rollback;

Rollback complete

 

SQL> update partitioned set z= decode(x,1,to_date('2008-10-01','yyyy-mm-dd'),
  2  2, to_date('2007-10-01','yyyy-mm-dd'));

update partitioned set z= decode(x,1,to_date('2008-10-01','yyyy-mm-dd'),
2, to_date('2007-10-01','yyyy-mm-dd'))

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

SQL> alter table partitioned enable row movement;

Table altered

SQL>
SQL>  update partitioned set z= decode(x,1,to_date('2008-10-01','yyyy-mm-dd'),
  2   2, to_date('2007-10-01','yyyy-mm-dd'));

2 rows updated

SQL>
SQL> commit;

Commit complete

SQL>  select rowid, a.* from partitioned a;

ROWID                                                    X                                       Y Z
------------------ --------------------------------------- --------------------------------------- -----------
AADl6vAAEAAAmVNAAB                                       2                                       1 2007-10-1
AADl6wAAEAAAmVVAAB                                       1                                       1 2008-10-1

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10173379/viewspace-619851/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10173379/viewspace-619851/