oracle 更新分区表关键字 ORA-14402

不知道大家在更新分区表关键字的时候有没有遇到过ORA-14402，今天我就给大家演示一下。

    首先创建分区表

    create table part_tab_rowid (id int,col2 int,col3 int,contents varchar2(4000))

        partition by range (id)

        (

        partition p1 values less than (10),

        partition p2 values less than (20),

        partition p_max values less than (maxvalue)

        )

        ;

insert into part_tab_rowid select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=30;

commit;

select t.id,t.rowid from part_tab_rowid partition (p1) t;

        ID ROWID

---------- ------------------

         1 AAAZdQAAGAAATxjAAA

         2 AAAZdQAAGAAATxjAAB

         3 AAAZdQAAGAAATxjAAC

         4 AAAZdQAAGAAATxjAAD

         5 AAAZdQAAGAAATxjAAE

         6 AAAZdQAAGAAATxjAAF

         7 AAAZdQAAGAAATxjAAG

         8 AAAZdQAAGAAATxjAAH

         9 AAAZdQAAGAAATxjAAI

已选择9行。

select t.id,t.rowid from part_tab_rowid partition (p2) t;

            ID ROWID

---------- ------------------

        10 AAAZdRAAGAAATxrAAA

        11 AAAZdRAAGAAATxrAAB

        12 AAAZdRAAGAAATxrAAC

        13 AAAZdRAAGAAATxrAAD

        14 AAAZdRAAGAAATxrAAE

        15 AAAZdRAAGAAATxrAAF

        16 AAAZdRAAGAAATxrAAG

        17 AAAZdRAAGAAATxrAAH

        18 AAAZdRAAGAAATxrAAI

        19 AAAZdRAAGAAATxrAAJ

已选择10行。

      大家注意一下，红色ID=1的记录 现在我们修改这条记录

      --以下这个enable row movement必须操作，否则会出现ORA-014402:更新分区关键字列导致分区更改

      alter table part_tab_rowid   enable row movement;

      update part_tab_rowid set id=12 where id=1;

      commit;

      看下结果

      select t.id,t.rowid from part_tab_rowid partition(p1) t;

        ID ROWID

---------- ------------------

         2 AAAZdQAAGAAATxjAAB

         3 AAAZdQAAGAAATxjAAC

         4 AAAZdQAAGAAATxjAAD

         5 AAAZdQAAGAAATxjAAE

         6 AAAZdQAAGAAATxjAAF

         7 AAAZdQAAGAAATxjAAG

         8 AAAZdQAAGAAATxjAAH

         9 AAAZdQAAGAAATxjAAI

已选择8行。

SQL> select t.id,t.rowid from part_tab_rowid partition(p2) t;

        ID ROWID

---------- ------------------

        10 AAAZdRAAGAAATxrAAA

        11 AAAZdRAAGAAATxrAAB

        12 AAAZdRAAGAAATxrAAC

        13 AAAZdRAAGAAATxrAAD

        14 AAAZdRAAGAAATxrAAE

        15 AAAZdRAAGAAATxrAAF

        16 AAAZdRAAGAAATxrAAG

        17 AAAZdRAAGAAATxrAAH

        18 AAAZdRAAGAAATxrAAI

        19 AAAZdRAAGAAATxrAAJ

        12 AAAZdRAAGAAATxrAAK

         已选择11行。

--由此可以发现，rowid的记录发生了改变。