默認情況下，oracle的分區(qū)表對于分區(qū)字段是不允許進行update操作的，如果有對分區(qū)字段行進update，就會報錯——ORA-14402:?更新分區(qū)關(guān)鍵字列將導致分區(qū)的更改。但是可以通過打開表的row?movement屬性來允許對分區(qū)字段的update操作。

例：創(chuàng)建分區(qū)表test_part進行實驗

create?table?TEST_PART
(
??A1?NUMBERnot?null,
??A2?DATE??????not?null,
??A3?VARCHAR2(6)?not?null,
??A4?DATE?not?null,
??A5?NUMBER?not?null,
)
partition?by?range?(A1)
(
??partition?P1?values?less?than?(1000),
??partition?P2?values?less?than?(2000),
??partition?P3?values?less?than?(3000),
??partition?P4?values?less?than?(4000),
??partition?P5?values?less?than?(5000),
??partition?P6?values?less?than?(MAXVALUE)
);

插入如下的數(shù)據(jù)
SQL>?select?*?from?test_part;

????????A1?A2??????????A3?????A4??????????????????A5
----------?-----------?------?-----------?----------
???????123?2006-06-30??123456?2006-06-30?????????123
???????456?2006-06-30??asdfgh?2006-06-30?????????456
?????????1?2006-06-30??234123?2006-06-30???????????1
?????????2?2006-06-30??234234?2006-06-30???????????2
??????1234?2006-06-30??456789?2006-06-30????????1234
??????1111?2006-06-30??ewrqwe?2006-06-30????????1111
??????2222?2006-06-30??fdafda?2006-06-30????????2222
??????3333?2006-06-30??342342?2006-06-30????????3333
??????5678?2006-06-30??qwerty?2006-06-30????????5678

9?rows?selected

分區(qū)P1、P2的數(shù)據(jù)分別為：
SQL>?select?rowid,t.*?from?test_part?partition(p1)?t;

ROWID??????????????????????A1?A2??????????A3?????A4??????????????????A5
------------------?----------?-----------?------?-----------?----------
AAAGLoAAGAAAtsEAAB????????456?2006-06-30??asdfgh?2006-06-30?????????456
AAAGLoAAGAAAtsEAAC??????????1?2006-06-30??234123?2006-06-30???????????1
AAAGLoAAGAAAtsEAAD??????????2?2006-06-30??234234?2006-06-30???????????2
AAAGLoAAGAAAtsEAAE????????123?2006-06-30??123456?2006-06-30?????????123

SQL>?select?rowid,t.*?from?test_part?partition(p2)?t;

ROWID??????????????????????A1?A2??????????A3?????A4??????????????????A5
------------------?----------?-----------?------?-----------?----------
AAAGLwAAGAAA+8MAAC???????1234?2006-06-30??456789?2006-06-30????????1234
AAAGLwAAGAAA+8MAAD???????1111?2006-06-30??ewrqwe?2006-06-30????????1111

直接update提示錯誤
SQL>?update?test_part?set?a1=1123?where?a1=123;

update?test_part?set?a1=1123?where?a1=123

ORA-14402:?更新分區(qū)關(guān)鍵字列將導致分區(qū)的更改

打開row?movement屬性
SQL>?alter?table?test_part?enable?row?movement;

Table?altered

再次執(zhí)行update操作
SQL>?update?test_part?set?a1=1123?where?a1=123;

1?row?updated

執(zhí)行是成功的并遷移到分區(qū)P2上了，且這時候rowid也發(fā)生了變化
SQL>?select?rowid,t.*?from?test_part?partition(p2)?t;

ROWID??????????????????????A1?A2??????????A3?????A4??????????????????A5
------------------?----------?-----------?------?-----------?----------
AAAGLwAAGAAA+8MAAC???????1234?2006-06-30??456789?2006-06-30????????1234
AAAGLwAAGAAA+8MAAD???????1111?2006-06-30??ewrqwe?2006-06-30????????1111
AAAGLwAAGAAA+8PAAB???????1123?2006-06-30??123456?2006-06-30?????????123

SQL>?

enable?row?movement可以允許數(shù)據(jù)段的壓縮、update分區(qū)字段的數(shù)據(jù)（跨分區(qū)的）

但是，也是有限制性的：對于普通表（heap-organized）行遷移后rowid會發(fā)生變化，對于索引表（index-organized）rowid雖然依然有效，但是其實際對應(yīng)的物理構(gòu)成是錯誤的。

轉(zhuǎn)載于:https://www.cnblogs.com/yifan268/archive/2008/06/14/1221114.html

總結(jié)

以上是生活随笔為你收集整理的分区表分区字段的update操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。