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

来源：互联网发布：php编写九九乘法表编辑：程序博客网时间：2024/06/06 01:56

不知道大家在更新分区表关键字的时候有没有遇到过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的记录发生了改变。

0 0