一、行遷移/行鏈接的介紹
在實(shí)際的工作中我們經(jīng)常會(huì)碰到一些Oracle數(shù)據(jù)庫(kù)性能較低的問(wèn)題，當(dāng)然，引起Oracle數(shù)據(jù)庫(kù)性能較低的原因是多方面的，我們能夠通過(guò)一些正確的設(shè)計(jì)和診斷來(lái)盡量的避免一些Oracle數(shù)據(jù)庫(kù)性能不好，Row Migration (行遷移) & Row Chaining (行鏈接)就是其中我們可以盡量避免的引起Oracle數(shù)據(jù)庫(kù)性能低下的潛在問(wèn)題。通過(guò)合理的診斷行遷移/行鏈接，我們可以較大幅度上提高Oracle數(shù)據(jù)庫(kù)的性能。
那究竟什么是行遷移/行鏈接呢，先讓我們從Oracle的block開(kāi)始談起。
操作系統(tǒng)的最小讀寫(xiě)操作單元是操作系統(tǒng)的block，所以當(dāng)創(chuàng)建一個(gè)Oracle數(shù)據(jù)庫(kù)的時(shí)候我們應(yīng)該講數(shù)據(jù)庫(kù)的block size設(shè)置成為操作系統(tǒng)的block size的整數(shù)倍，Oracle block是Oracle數(shù)據(jù)庫(kù)中讀寫(xiě)操作的最小單元，Oracle9i之前的Oracle數(shù)據(jù)庫(kù)版本中Oracle block一旦在創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候被設(shè)定后就沒(méi)法再更改。為了在創(chuàng)建數(shù)據(jù)庫(kù)之前確定一個(gè)合理的Oracle block的大小，我們需要考慮一些因素，例如數(shù)據(jù)庫(kù)本身的大小以及并發(fā)事務(wù)的數(shù)量等。使用一個(gè)合適的Oracle block大小對(duì)于數(shù)據(jù)庫(kù)的調(diào)優(yōu)是非常重要的。Oracle block的結(jié)構(gòu)如下圖所示：
圖一：Oracle Block結(jié)構(gòu)圖
由上圖我們可以看出，一個(gè)Oracle block由三個(gè)部分組成，分別是數(shù)據(jù)塊頭、自由空間、實(shí)際數(shù)據(jù)三部份組成。
數(shù)據(jù)塊頭：主要包含有數(shù)據(jù)塊地址的一些基本信息和段的類(lèi)型，以及表和包含有數(shù)據(jù)的實(shí)際行的地址。
自由空間：是指可以為以后的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個(gè)參數(shù)影響。
實(shí)際數(shù)據(jù)：是指在行內(nèi)存儲(chǔ)的實(shí)際數(shù)據(jù)。
當(dāng)創(chuàng)建或者更改任何表和索引的時(shí)候，Oracle在空間控制方面使用兩個(gè)存儲(chǔ)參數(shù)：
PCTFREE:為將來(lái)更新已經(jīng)存在的數(shù)據(jù)預(yù)留空間的百分比。
PCTUSED:用于為插入一新行數(shù)據(jù)的最小空間的百分比。這個(gè)值決定了塊的可用狀態(tài)。可用的塊時(shí)可以執(zhí)行插入的塊，不可用狀態(tài)的塊只能執(zhí)行刪除和修改，可用狀態(tài)的塊被放在freelist中。
當(dāng)表中一行的數(shù)據(jù)不能在一個(gè)數(shù)據(jù)block中放入的時(shí)候，這個(gè)時(shí)候就會(huì)發(fā)生兩種情況，一種是行鏈接，另外一種就是行遷移了。
行鏈接產(chǎn)生在第一次插入數(shù)據(jù)的時(shí)候假如一個(gè)block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個(gè)或者多個(gè)在這個(gè)段中保留的block存儲(chǔ)這一行記錄，行鏈接比較輕易發(fā)生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數(shù)據(jù)類(lèi)型的字段，這種時(shí)候行鏈接是不可避免的會(huì)產(chǎn)生的。
當(dāng)一行記錄初始插入的時(shí)候事可以存儲(chǔ)在一個(gè)block中的，由于更新操作導(dǎo)致行長(zhǎng)增加了，而block的自由空間已經(jīng)完全滿了，這個(gè)時(shí)候就產(chǎn)生了行遷移。在這種情況下，Oracle將會(huì)遷移整行數(shù)據(jù)到一個(gè)新的block中（假設(shè)一個(gè)block中可以存儲(chǔ)下整行數(shù)據(jù)），Oracle會(huì)保留被遷移行的原始指針指向新的存放行數(shù)據(jù)的block，這就意味著被遷移行的ROW ID是不會(huì)改變的。
當(dāng)發(fā)生了行遷移或者行鏈接，對(duì)這行數(shù)據(jù)操作的性能就會(huì)降低，因?yàn)镺racle必須要掃描更多的block來(lái)獲得這行的信息。
下面舉例來(lái)具體說(shuō)明行遷移/行鏈接的產(chǎn)生過(guò)程。
先創(chuàng)建一個(gè)pctfree為20和pctused為50的測(cè)試表：
create table test（
col1 char(20),
col2 number)
storage (
pctfree 20
pctused 50);
當(dāng)插入一條記錄的時(shí)候，Oracle會(huì)在free list中先去尋找一個(gè)自由的塊，并且將數(shù)據(jù)插入到這個(gè)自由塊中。而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間達(dá)到pctfree的值，此塊就會(huì)從free list中移走，而當(dāng)此塊中的使用空間低于pctused的時(shí)候，此塊又被重新放到free list中。
Oracle使用free list機(jī)制可以大大的提高性能，對(duì)于每次的插入操作，Oracle只需要查找free list就可以了，而不是去查找所有的block來(lái)尋找自由空間。
假設(shè)第一次插入數(shù)據(jù)使用的一個(gè)空的block，如下圖所示：
圖二：Oracle空的block結(jié)構(gòu)圖
假設(shè)插入第一條記錄的時(shí)候占用一個(gè)block的10%的空間（除去block頭占去的大?。?，剩余的自由空間90%大于pctfree20%，因此這個(gè)block還將繼續(xù)為下次的插入操作提供空間。
圖三：插入10%后的Oracle block結(jié)構(gòu)圖
再連續(xù)插入七條記錄，使block的剩余自由空間剩下20%，此時(shí)，這個(gè)block將要從free list中移走，假如再插入記錄，Oracle將再free list中尋找下一個(gè)空余的block去存放后來(lái)插入的數(shù)據(jù)。
圖四：插入80%后的Oracle block結(jié)構(gòu)圖
此時(shí)假如去更新第一條插入的記錄，使其行長(zhǎng)增加15%,Oracle將會(huì)使用這個(gè)block中剩余的20%的自由空間來(lái)存放此行數(shù)據(jù)，假如再更新第二條記錄，同樣的使其行長(zhǎng)增加15%,而此block中只剩下5%的自由空間，不夠存放更新的第二條記錄，于是Oracle會(huì)在free list中尋找一個(gè)有自由空間（10%+15%）的block來(lái)存放這行記錄的block去存儲(chǔ)，在原來(lái)的block中保存了指向新的block的指針，原來(lái)這行記錄的ROW ID保持不變，這個(gè)時(shí)候就產(chǎn)生了行遷移。
而當(dāng)我們插入一條新紀(jì)錄的時(shí)候，假如一個(gè)blcok不足以存放下這條記錄，Oracle就會(huì)尋找一定數(shù)量的block一起來(lái)容納這條新的記錄，這個(gè)時(shí)候就產(chǎn)生了行鏈接，行鏈接主要產(chǎn)生在LOB、CLOB、BLOB和大的VA行鏈接HAR2數(shù)據(jù)類(lèi)型上。
具體我們通過(guò)下面的一個(gè)試驗(yàn)來(lái)查看行鏈接和行遷移是如何產(chǎn)生并在數(shù)據(jù)文件中體現(xiàn)出來(lái)的。
先查看ALLAN這個(gè)表空間的數(shù)據(jù)文件號(hào)，為了便于測(cè)試，我只建立了一個(gè)數(shù)據(jù)文件。
SQL> select file_id from dba_data_files where tablespace_name='ALLAN';
FILE_ID
----------
23
創(chuàng)建一個(gè)測(cè)試表test：
SQL> create table test ( x int PRimary key, a char(2000), b char(2000), c char(2000), d char(2000), e char(2000) ) tablespace allan;
Table created.
因?yàn)槲业臄?shù)據(jù)庫(kù)的db_block_size是8K，所以我創(chuàng)建的表有五個(gè)字段，每個(gè)占2000個(gè)字節(jié)，這樣一行記錄大約10K,就能超過(guò)一個(gè)block的大小了。
然后插入一行記錄，只有一個(gè)字段的：
SQL> insert into test(x) values (1);
1 row created.
SQL> commit;
Commit complete.
查找這行記錄所在的block，并dump出來(lái)：
SQL> select dbms_rowid.rowid_block_number(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
34
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下查看trace文件的內(nèi)容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fSEO=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
對(duì)其中的一些信息做一些解釋?zhuān)?BR>
Fb:H是指行記錄的頭，L是指行記錄的最后一列，F(xiàn)是指行記錄的第一列。
Cc：列的數(shù)量
Nrid：對(duì)于行鏈接或者行遷移來(lái)說(shuō)的下一個(gè)row id的值
由上面的dump信息我們可以看出來(lái)當(dāng)前表test是沒(méi)有行鏈接或者行遷移的。
然后更新test表,并重新dump出來(lái)：
SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;
1 row updated.
SQL> commit;
Commit complete.
此時(shí)應(yīng)該有行遷移/行鏈接產(chǎn)生了。
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下查看trace文件的內(nèi)容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f3
0x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442b
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x178a
avsp=0x177c
tosp=0x177c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x178a
block_row_dump:
tab 0, row 0, @0x178a
tl: 2064 fb: --H-F--N lb: 0x2 cc: 3
nrid: 0x05c00023.0
col 0: [ 2] c1 02
col 1: [2000]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
…………
col 2: [48]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
我們不難看出，nrid出現(xiàn)了值，指向了下一個(gè)row id,證實(shí)剛剛的update操作使這行記錄產(chǎn)生了行鏈接或者行遷移了。
二、行遷移/行鏈接的檢測(cè)
通過(guò)前面的介紹我們知道，行鏈接主要是由于數(shù)據(jù)庫(kù)的db_block_size不夠大，對(duì)于一些大的字段沒(méi)法在一個(gè)block中存儲(chǔ)下，從而產(chǎn)生了行鏈接。對(duì)于行鏈接我們除了增大db_block_size之外沒(méi)有別的任何辦法去避免，但是因?yàn)閿?shù)據(jù)庫(kù)建立后db_block_size是不可改變的（在9i之前），對(duì)于Oracle9i的數(shù)據(jù)庫(kù)我們可以對(duì)不同的表空間指定不同的db_block_size,因此行鏈接的產(chǎn)生幾乎是不可避免的，也沒(méi)有太多可以調(diào)整的地方。行遷移則主要是由于更新表的時(shí)候，由于表的pctfree參數(shù)設(shè)置太小，導(dǎo)致block中沒(méi)有足夠的空間去容納更新后的記錄，從而產(chǎn)生了行遷移。對(duì)于行遷移來(lái)說(shuō)就非常有調(diào)整的必要了，因?yàn)檫@個(gè)是可以調(diào)整和控制清除的。
如何檢測(cè)數(shù)據(jù)庫(kù)中存在有了行遷移和行鏈接呢？我們可以利用Oracle數(shù)據(jù)庫(kù)自身提供的腳本utlchain.sql（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表，然后利用ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個(gè)分析表，將分析的結(jié)果存入chained_rows表中。從utlchain.sql腳本中我們看到chained_rows的建表腳本，對(duì)于分區(qū)表，cluster表都是適用的。然后可以使用拼湊語(yǔ)句的辦法生成分析所需要的表的腳本，并執(zhí)行腳本將具體的分析數(shù)據(jù)放入Chained_rows表中，例如下面是分析一個(gè)用戶下所有表的腳本：
SPOOL list_migation_rows.sql
SET ECHO OFF
SET HEADING OFF
SELECT 'ANALYZE TABLE ' table_name ' LIST CHAINED ROWS INTO chained_rows;' FROM user_tables;
SPOOL OFF
然后查詢chained_rows表，可以具體查看某張表上有多少的行鏈接和行遷移。
SELECT table_name, count(*) from chained_rows GROUP BY table_name;
當(dāng)然，也可以查詢v$sysstat視圖中的’table fetch continued row’列得到當(dāng)前的行鏈接和行遷移數(shù)量。
SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
可以使用如下的腳本來(lái)直接查找存在有行鏈接和行遷移的表，自動(dòng)完成所有的分析和統(tǒng)計(jì)。
accept owner prompt " Enter the schema name to check for Row Chaining (RETURN for All): "
prompt
prompt
accept table prompt " Enter the table name to check (RETURN for All tables owned by &owner): "
prompt
prompt
set head off serverout on term on feed off veri off echo off
!clear
prompt
declare
v_owner varchar2(30);
v_table varchar2(30);
v_chains number;
v_rows number;
v_count number := 0;
sql_stmt varchar2(100);
dynamicCursor INTEGER;
dummy INTEGER;
cursor chains is
select count(*) from chained_rows;
cursor analyze is
select owner, table_name
from sys.dba_tables
where owner like upper('%&owner%')
and table_name like upper('%&table%')
order by table_name;
begin
dbms_output.enable(64000);
open analyze;
fetch analyze into v_owner, v_table;
while analyze%FOUND loop
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'analyze table 'v_owner'.'v_table' list chained rows into chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
open chains;
fetch chains into v_chains;
if (v_chains != 0) then
if (v_count = 0) then
dbms_output.put_line(CHR(9)CHR(9)CHR(9)'<<<<< Chained Rows Found >>>>>');
v_count := 1;
end if;
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'Select count(*) v_rows'' From 'v_owner'.'v_table;
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);
dummy := dbms_sql.execute(dynamicCursor);
dummy := dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner'.'v_table);
dbms_output.put_line(CHR(9)'---> Has 'v_chains' Chained Rows and 'v_rows' Num_Rows in it!');
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'truncate table chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains := 0;
end if;
close chains;
fetch analyze into v_owner, v_table;
end loop;
if (v_count = 0) then
dbms_output.put_line('No Chained Rows found in the 'v_owner' owned Tables!');
end if;
close analyze;
end;
/
set feed on head on
prompt
三、行遷移和行鏈接的清除
由于對(duì)于行鏈接來(lái)說(shuō)只能增大db_block_size來(lái)清除，而db_block_size在創(chuàng)建了數(shù)據(jù)庫(kù)后又是不能改變了的，所以這里對(duì)行鏈接的清除不做過(guò)多的敘述了，主要是針對(duì)行遷移來(lái)談?wù)勗趯?shí)際的生產(chǎn)系統(tǒng)中如何去清除。
對(duì)于行遷移的清除，一般來(lái)說(shuō)分為兩個(gè)步驟：第一步，控制住行遷移的增長(zhǎng)，使其不在增多；第二步，清除掉以前存在的行遷移。
眾所周知，行遷移產(chǎn)生的主要原因是因?yàn)楸砩系膒ctfree參數(shù)設(shè)置過(guò)小導(dǎo)致的，而要實(shí)現(xiàn)第一步控制住行遷移的增長(zhǎng)，就必須設(shè)置好一個(gè)正確合適的pctfree參數(shù)，否則即使清除了當(dāng)前的行遷移后馬上又會(huì)產(chǎn)生很多新的行遷移。當(dāng)然，這個(gè)參數(shù)也不是越大越好的，假如pctfree設(shè)置的過(guò)大，會(huì)導(dǎo)致數(shù)據(jù)塊的利用率低，造成空間的大量浪費(fèi)，因此必須設(shè)置一個(gè)合理的pctfree參數(shù)。如何去確定一個(gè)表上合理的pctfree參數(shù)呢，一般來(lái)說(shuō)有兩種方法。
第一種是定量的的設(shè)定方法，就是利用公式來(lái)設(shè)定pctfree的大小。先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來(lái)分析要修改pctfree的表,然后查看user_tables中的AVG_ROW_LEN列值，得到一個(gè)平均行長(zhǎng)AVG_ROW_LEN1，然后大量的對(duì)表操作之后，再次使用上述命令分析表，得到第二個(gè)平均行長(zhǎng)AVG_ROW_LEN2，然后運(yùn)用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）得出的結(jié)果就是定量計(jì)算出來(lái)的一個(gè)合適的pctfree的值。這種方法因?yàn)槭嵌坑?jì)算出來(lái)的，可能不一定會(huì)很準(zhǔn)確，而且因?yàn)橐治霰恚詫?duì)于使用RBO執(zhí)行計(jì)劃的系統(tǒng)不是很適用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，則平均修改量為 10，PCTFREE 應(yīng)調(diào)整為 100 * 10 /（10 + 60）= 16.7% 。
第二種是差分微調(diào)的方法，先查詢到當(dāng)前表的pctfree的值，然后監(jiān)控和調(diào)整pctfree參數(shù)，每次增加一點(diǎn)pctfree的大小，每次增加的比例不要超過(guò)5個(gè)百分點(diǎn)，然后使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行鏈接的增長(zhǎng)情況，對(duì)于不同的表采取不同的增長(zhǎng)比例，對(duì)于行遷移增長(zhǎng)的比較快的表pctfree值就增加的多點(diǎn)，對(duì)于增長(zhǎng)慢的表就增加的少點(diǎn)，直到表的行遷移基本保持不增長(zhǎng)了為止。但是注重不要把pctfree調(diào)的過(guò)大，一般在40%以下就可以了，否則會(huì)造成空間的很大浪費(fèi)和增加數(shù)據(jù)庫(kù)訪問(wèn)的IO。
使用上述的方法控制住了當(dāng)前表的行遷移的增長(zhǎng)之后，就可以開(kāi)始清除之前表上存在的行遷移了。是否清除掉行遷移，關(guān)系到系統(tǒng)的性能是否能夠有很大的提高。因此，對(duì)于以前存在的行遷移是一定而且必須要清除掉的。清除掉已經(jīng)存在的行遷移有很多方法，但是并不是所有的方法都能適用所有的情況，例如表中的記錄數(shù)多少，表上的關(guān)聯(lián)多少、表上行遷移的數(shù)量多少等等這些因素都會(huì)是成為制約你使用什么方法清除的條件，因此，根據(jù)表的特點(diǎn)和具體情況的不同我們應(yīng)該采用不同的方法去清除行遷移。下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況。
方法一：傳統(tǒng)的清除行遷移的方法
具體步驟如下：
1． 執(zhí)行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql腳本創(chuàng)建chained_rows表。
@$ORACLE_HOME/rdbms/admin/utlchain.sql
2． 將存在有行遷移的表（用table_name代替）中的產(chǎn)生行遷移的行的rowid放入到chained_rows表中。
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;
3． 將表中的行遷移的row id放入臨時(shí)表中保存。
CREATE TABLE table_name_temp AS
SELECT * FROM table_name
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'table_name');
4． 刪除原來(lái)表中存在的行遷移的記錄行。
DELETE table_name
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'table_name');
5． 從臨時(shí)表中取出并重新插入那些被刪除了的數(shù)據(jù)到原來(lái)的表中，并刪除臨時(shí)表。
INSERT INTO table_name SELECT * FROM table_name_temp;
DROP TABLE table_name_temp;
對(duì)于這種傳統(tǒng)的清除RM的方法，優(yōu)點(diǎn)是執(zhí)行起來(lái)過(guò)程比較簡(jiǎn)單，輕易實(shí)現(xiàn)。但是這種算法的缺陷是沒(méi)有考慮到表關(guān)聯(lián)的情況，在大多數(shù)數(shù)據(jù)庫(kù)中很多表都是和別的表之間有表關(guān)聯(lián)的，有外鍵的限制，這樣就造成在步驟3中根本無(wú)法delete掉存在有行遷移的記錄行，所以這種方法能夠適用的表的范圍是有限的，只能適用于表上無(wú)任何外鍵關(guān)聯(lián)的表。由于這種方法在插入和刪除數(shù)據(jù)的時(shí)候都沒(méi)有disable掉索引，這樣導(dǎo)致主要消耗時(shí)間是在刪除和插入時(shí)維持索引樹(shù)的均衡上了，這個(gè)對(duì)于假如記錄數(shù)不多的情況時(shí)間上還比較短，但是假如對(duì)于記錄數(shù)很多的表這個(gè)所消耗的時(shí)間就不是能夠接受的了。顯然，這種方法在處理大數(shù)據(jù)量的表的時(shí)候顯然是不可取的。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Table created.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows；
TABLE_NAME COUNT(*)
------------------------------ ----------
CUSTOMER 21306
1 rows selected.
查看在CUSTOMER表上存在的限制:
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_CUSTOMER1 P CUSTOMER
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_CUSTOMER1';
no rows selected
SQL> CREATE TABLE CUSTOMER_temp AS
SELECT * FROM CUSTOMER WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'CUSTOMER');
Table created.
SQL>select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> DELETE CUSTOMER WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'CUSTOMER');
21306 rows deleted.
SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;
21306 rows created.
SQL> DROP TABLE CUSTOMER_temp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> truncate table chained_rows;
Table truncated.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
以上整個(gè)清除兩萬(wàn)多行的行遷移過(guò)程在三分鐘左右，而且全部都在聯(lián)機(jī)的狀態(tài)下完成，基本上不會(huì)對(duì)業(yè)務(wù)有什么影響，唯一就是在要清除行遷移的表上不能有對(duì)外鍵的限制，否則就不能采用這個(gè)方法去清除了。
方法二：改進(jìn)了的傳統(tǒng)清除行遷移的方法
1． 執(zhí)行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql腳本創(chuàng)建chained_rows表。
2． 禁用所有其它表上關(guān)聯(lián)到此表上的所有限制。
3． 將表中的行遷移的row id放入臨時(shí)表中保存。
4． 刪除原來(lái)表中存在的行遷移的記錄行。
5． 從臨時(shí)表中取出并重新插入那些被刪除了的數(shù)據(jù)到原來(lái)的表中，并刪除臨時(shí)表。
6． 啟用所有其它表上關(guān)聯(lián)到此表上的所有限制。
這種算法是對(duì)傳統(tǒng)算法的一種改進(jìn)，對(duì)于使用這種算法來(lái)清除行遷移，考慮到了表之間的關(guān)聯(lián)，還可以靈活的利用的TOAD工具生成的表關(guān)聯(lián)信息，是一種比較適合于清除行遷移的一種方法。但是因?yàn)槭褂眠@種方法后來(lái)需要重建索引，假如記錄數(shù)很大，比如說(shuō)上千萬(wàn)條以上的記錄的表，就不是很合適了，因?yàn)檫@個(gè)重建索引的時(shí)間會(huì)很長(zhǎng)，是線性時(shí)間復(fù)雜度的，而重建索引是會(huì)導(dǎo)致索引所在的表被鎖定的，從而導(dǎo)致插入不了新的記錄，重建索引的時(shí)間太長(zhǎng)導(dǎo)致記錄長(zhǎng)時(shí)間插入不了是會(huì)嚴(yán)重影響應(yīng)用的，甚至導(dǎo)致數(shù)據(jù)的丟失，因此這個(gè)是使用這個(gè)方法前必須要考慮到的一個(gè)重要因素；對(duì)于8i以上的版本可以使用online的方法來(lái)重建索引，這樣不會(huì)導(dǎo)致鎖表，但是會(huì)有額外更多的開(kāi)銷(xiāo)，時(shí)間會(huì)很長(zhǎng)。再者，因?yàn)檫@種方法在插入記錄和刪除記錄都是帶著索引的，假如表上的行遷移比較多，這樣耗時(shí)間會(huì)比較長(zhǎng)，而且占用資源也會(huì)比較大，因此只適用于表上行遷移存在的比較少的表?？偟膩?lái)說(shuō)，這種方法對(duì)于表記錄太多或者是表上的行遷移太多的情況都不是很適用，比較適合表記錄少和表上行遷移都不太多的情況。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>select index_name,index_type,table_name from user_indexes where table_name='TERMINAL';
INDEX_NAME INDEX_TYPE TABLE_NAME
-----------------------------------------------------------------
INDEX_TERMINAL_TERMINALCODE NORMAL TERMINAL
I_TERMINAL_ID_TYPE NORMAL TERMINAL
I_TERMINAL_OT_OID NORMAL TERMINAL
PK_TERMINAL_ID NORMAL TERMINAL
UI_TERMINAL_GOODIS_SSN NORMAL TERMINAL
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_TERMINAL_ID';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C003200 R CONN
SQL>alter table CONN disable constraint SYS_C003200;
Table altered.
SQL>CREATE TABLE TERMINAL_temp AS
SELECT * FROM TERMINAL
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'TERMINAL');
Table created.
SQL>select count(*) from TERMINAL_temp;
COUNT(*)
----------
8302
SQL>DELETE TERMINAL
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'TERMINAL');
8302 rows deleted.
SQL>INSERT INTO TERMINAL SELECT * FROM TERMINAL_temp;
8302 rows created.
SQL>alter table CONN disable constraint SYS_C003200;
Table altered.
SQL>select count(*) from terminal;
COUNT(*)
----------
647799
SQL>truncate table chained_rows;
Table truncated.
SQL>ANALYZE TABLE TERMINAL LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>select count(*) from chained_rows;
COUNT(*)
----------
0
從上面過(guò)程中可以看出，對(duì)TERMINAL這張表的行遷移清除耗時(shí)總共不到五分鐘的時(shí)間，總體來(lái)說(shuō)還是比較快的。從我在生產(chǎn)數(shù)據(jù)庫(kù)中清除行遷移的經(jīng)驗(yàn)來(lái)說(shuō)，這種方法基本適用于大部分存在有行遷移的表。
方法三：使用TOAD工具清除行遷移的方法
1． 備份要清除RM的表。
RENAME table_name TO table_name_temp;
2． Drop 所有其它表上關(guān)聯(lián)到table_name的外鍵限制。
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in (SELECT CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME='table_name' AND CONSTRAINT_TYPE=’P’);
ALTER TABLE table_name DROP CONSTRAINT XXXX;（XXXX為上述的查詢結(jié)果）
3． 重建1中被rename的表。
CREATE TABLE table_name AS SELECT * FROM table_name_temp WHERE 0 = 1;
4． 重建表中原來(lái)的數(shù)據(jù)。
INSERT /*+ APPEND */ INTO table_name SELECT * FROM table_name_temp;
5． 刪除在table_name_temp上的索引和關(guān)聯(lián)其他表的外鍵。
6． 在table_name上建立和原來(lái)一樣的索引、主鍵和所有的外鍵限制。
7． 重新編譯相關(guān)的存儲(chǔ)過(guò)程、函數(shù)和包。
8． 刪除表table_name_temp。
對(duì)于使用這種方法來(lái)清除行遷移，全部的代碼都是可以由TOAD工具來(lái)生成的。由于此方法把表上的關(guān)聯(lián)考慮進(jìn)去了，也是一種比較的全面的考慮的一種清除方法，而且在清除過(guò)程中重建了表和索引，對(duì)于數(shù)據(jù)庫(kù)的存儲(chǔ)和性能上都有提高。因?yàn)檫@種方法一開(kāi)始是rename表為臨時(shí)表，然后重建一個(gè)新表出來(lái)的，因此需要兩倍的表的空間，因此在操作之前一定要檢查要清除的表所在的表空間的free空間是否足夠；但是也有一定的缺陷，因?yàn)樵谛卤碇兄匦虏迦朐瓉?lái)的數(shù)據(jù)后需要重建索引和限制，因此在時(shí)間和磁盤(pán)的空間上都有比較大的開(kāi)銷(xiāo)，而且對(duì)于前臺(tái)的應(yīng)用可能會(huì)有一段時(shí)間的中斷，當(dāng)然，這個(gè)中斷時(shí)間就主要是消耗在重建索引和重建限制上了，而時(shí)間的長(zhǎng)短跟需要重建索引和限制的多少以及表的記錄多少等等因素都有關(guān)系。使用這種方法對(duì)于7*24小時(shí)要求的系統(tǒng)上清除行遷移不是很合適，因?yàn)槭褂眠@種方法會(huì)導(dǎo)致系統(tǒng)可能有一段時(shí)間的停機(jī)，假如系統(tǒng)的實(shí)時(shí)性比較高，這種方法就不是很適用了。
方法四：使用EXP/IMP工具清除行遷移的方法
1． 使用EXP導(dǎo)出存在有行遷移的表。
2． 然后TRUNCATE原來(lái)的表。
3． IMP開(kāi)始導(dǎo)出的表。
4． 重建表上所有的索引。（可選）
使用這種方法可以不用重建索引，省去了這部分時(shí)間，但是完成之后索引的使用效率不會(huì)很高，最好是在以后逐步的在線重建索引，這樣是可以不需要中斷業(yè)務(wù)的。但是需要考慮的是IMP的時(shí)候會(huì)比較慢，而且會(huì)占用比較大的IO，應(yīng)該選擇在應(yīng)用不是很繁忙的時(shí)候做這項(xiàng)工作，否則會(huì)對(duì)應(yīng)用的正常運(yùn)行產(chǎn)生較大的影響。對(duì)于這種方法還存在有一個(gè)比較大的弊端，就是在EXP表的時(shí)候要保證該表是沒(méi)有數(shù)據(jù)的更新或者是只讀狀態(tài)的，不能對(duì)表有插入或者更新操作，否則會(huì)導(dǎo)致數(shù)據(jù)的丟失。
SQL> select count(*) from test;
COUNT(*)
----------
169344
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
3294
$ exp allan/allan file=test.dmp tables=test
Export: Release 9.2.0.3.0 - ProdUCtion on Sun Jun 6 13:50:08 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST 169344 rows exported
Export terminated successfully without warnings.
$ sqlplus allan/allan
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Jun 6 13:50:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> truncate table test;
Table truncated.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
$ imp allan/allan file=test.dmp full=y ignore=y buffer=5000000
Import: Release 9.2.0.3.0 - Production on Sun Jun 6 13:51:24 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ALLAN's objects into ALLAN
. . importing table "TEST" 169344 rows imported
Import terminated successfully without warnings.
$ sqlplus allan/allan
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Jun 6 13:52:53 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> select count(*) from test;
COUNT(*)
----------
169344
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
OBJ_INDEX
SQL> alter index OBJ_INDEX rebuild online;
Index altered.
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
方法五：使用MOVE命令來(lái)清除行遷移的方法
1． 查看要清除行遷移的表所在的表空間。
Select table_name,tablespace_name from user_tables where table_name='table_name’;
2． 查看要清除行遷移的表上的具體索引。
select index_name,table_name from user_indexes where table_name＝‘table_name’;
3． Move要清除RM的表到指定的表空間中去。
alter table table_name move tablespace tablespace_name;
4． 重建表上的所有索引。
alter index index_name rebuild;
這種方法適用于8i及其以上的數(shù)據(jù)庫(kù)版本，主要是利用數(shù)據(jù)庫(kù)的一個(gè)MOVE命令來(lái)實(shí)現(xiàn)行遷移的清除的，MOVE命令的實(shí)質(zhì)其實(shí)就是INSERT … SELECT的一個(gè)過(guò)程，在MOVE表的過(guò)程中是需要兩倍的原來(lái)的表大小的，因?yàn)橹虚g過(guò)程是要保留原來(lái)的舊表的，新表創(chuàng)建完成后舊表就被刪除并釋放空間了。MOVE的時(shí)候要注重后面一定要加上表空間參數(shù)，所以必須要先知道表所在的表空間；因?yàn)镸OVE表之后需要重建索引，所以之前要確定表上的所有的索引。
這種方法對(duì)于表記錄數(shù)很大或者表上索引太多的情況不太適用，因?yàn)楸旧淼腗OVE就會(huì)很慢， 而且MOVE表的時(shí)候會(huì)要鎖定表，時(shí)間長(zhǎng)了會(huì)導(dǎo)致對(duì)表的其他操作出現(xiàn)問(wèn)題，導(dǎo)致數(shù)據(jù)插入不了丟失數(shù)據(jù)；MOVE表后還要重建索引，索引太多了的話重建的時(shí)間也會(huì)太長(zhǎng)；再者，這個(gè)方法也比較消耗資源，因此強(qiáng)烈建議在業(yè)務(wù)不繁忙的時(shí)候再執(zhí)行。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows;
COUNT(*)
----------
9145
SQL>select table_name,tablespace_name from user_tables where table_name='SERVICE';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SERVICE DATA
SQL>select index_name,table_name from user_indexes where table_name='SERVICE';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_SERVICE_ACCOUNTNUM SERVICE
I_SERVICE_DATEACTIVATED SERVICE
I_SERVICE_SC_S SERVICE
I_SERVICE_SERVICECODE SERVICE
PK_SERVICE_SID SERVICE
SQL>select count(*) from SERVICE;
COUNT(*)
----------
518718
SQL>alter table SERVICE move tablespace DATA;
Table altered.
SQL>alter index I_SERVICE_ACCOUNTNUM rebuild;
Index altered.
SQL>alter index I_SERVICE_DATEACTIVATED rebuild;
Index altered.
SQL>alter index I_SERVICE_SC_S rebuild;
Index altered.
SQL>alter index I_SERVICE_SERVICECODE rebuild;
Index altered.
SQL>alter index PK_SERVICE_SID rebuild;
Index altered.
SQL>truncate table chained_rows;
Table truncated.
SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows;
COUNT(*)
----------
0
利用MOVE命令來(lái)清除行遷移，執(zhí)行的命令都相對(duì)比較的簡(jiǎn)單，上面的例子中清除表SERVCIE中的行遷移的時(shí)間大概在五分鐘左右，其中move命令執(zhí)行的時(shí)間為不到兩分鐘，也就是鎖表的時(shí)間大概是不到兩分鐘，對(duì)于大多數(shù)的應(yīng)用來(lái)說(shuō)一般問(wèn)題都是不大的，放在系統(tǒng)閑的時(shí)候執(zhí)行基本上不會(huì)對(duì)應(yīng)用產(chǎn)生什么太多的影響。
方法六：對(duì)于一些行遷移數(shù)量巨大而且表記錄數(shù)巨大的表的行遷移的清除方法
1． 使用TOAD工具或者別的方法獲取存在有大量行遷移并且表記錄很大的表的重建表的SQL，然后保存為腳本。
2． 使用RENAME命令將原始表重命名為一個(gè)備份表，然后刪除別的表對(duì)原始表上的限制、以及原始表上的外鍵和索引。
3． 利用1中生成的腳本重建原始表，以及表上的限制，外鍵，索引等對(duì)象。
4． 然后按表模式導(dǎo)出2中備份的表，然后導(dǎo)入到另外的一個(gè)臨時(shí)中轉(zhuǎn)的數(shù)據(jù)庫(kù)庫(kù)中，因?yàn)楸淼拿忠呀?jīng)改變，所以導(dǎo)入后需要RENAME表為原來(lái)的名字，然后重新導(dǎo)出，最后再導(dǎo)入到原來(lái)的數(shù)據(jù)庫(kù)中。
這種方法主要是用來(lái)針對(duì)一些數(shù)據(jù)量比較大，并且表上的行遷移也比較多的表的行遷移清除。對(duì)于這些大表的行遷移的清除，正常來(lái)說(shuō)都需要停應(yīng)用一段較長(zhǎng)時(shí)間才能夠清除掉，讓人感覺(jué)比較的頭疼，對(duì)于7*24小時(shí)的應(yīng)用來(lái)說(shuō)，down機(jī)的時(shí)間越長(zhǎng)損失則越大，當(dāng)然是要盡量的減短down機(jī)的時(shí)間。但是因?yàn)楸肀旧肀容^大，不管怎樣做什么操作都是會(huì)比較耗費(fèi)時(shí)間和資源的，但是假如應(yīng)用在某段時(shí)間內(nèi)主要是以插入數(shù)據(jù)為主，更新數(shù)據(jù)和刪除數(shù)據(jù)都很少的，因此可以考慮可以采用這么一種方法：先重命名表，然后重新建立一個(gè)和原來(lái)一樣的表，用來(lái)保證之后的應(yīng)用的數(shù)據(jù)是可以正常插入的，從而使應(yīng)用不用停很久，因?yàn)橹亟ㄒ粋€(gè)沒(méi)有任何數(shù)據(jù)的表結(jié)構(gòu)的過(guò)程是很短暫的，大概需要幾秒鐘的時(shí)間，而重建好表了后就能保證應(yīng)用能夠正常的寫(xiě)入數(shù)據(jù)，從而使應(yīng)用幾乎不用停頓，然后把開(kāi)始重命名的原始表按表模式導(dǎo)出，因?yàn)楸淼拿忠呀?jīng)被改變，因此需要一個(gè)臨時(shí)庫(kù)來(lái)導(dǎo)入這些數(shù)據(jù)，然后重命名回原來(lái)的名字，然后按原來(lái)的表名導(dǎo)出后再重新導(dǎo)入原始數(shù)據(jù)庫(kù)，這樣操作起來(lái)雖然會(huì)比較麻煩，但是卻是一種很有效很實(shí)際的方法，速度也很快，導(dǎo)出后導(dǎo)入，因?yàn)楸旧肀斫Y(jié)構(gòu)已經(jīng)建立好了，不需要其他任何的多的操作，而且最要害的是這種方法所需要的down機(jī)時(shí)間是最短的。
SQL>ALTER TABLE USER.PAY RENAME TO PAY_X ;
然后導(dǎo)出PAY_X表;
$ exp USER/USER file=PAY_X.dmp tables=PAY_X
SQL>ALTER TABLE USER.BATCHPAYMENTDETAIL DROP CONSTRAINT FK_BATCHPAYMENTDETAIL_OPAYID ;
SQL>ALTER TABLE USER.DEPOSITCLASSIFY DROP CONSTRAINT FK_DEPOSITCLASSIFY2 ;
SQL>ALTER TABLE USER.DEPOSITCREDITLOG DROP CONSTRAINT FK_DEPOSITCREDITLOG2 ;
SQL>ALTER TABLE USER.DEPOSIT DROP CONSTRAINT SYS_C003423 ;
SQL>ALTER TABLE USER.PAY_X DROP CONSTRAINT SYS_C003549 ;
SQL>DROP INDEX USER.I_PAY_STAFFID ;
SQL>CREATE TABLE USER.PAY
(
PAYID NUMBER(9),
ACCOUNTNUM NUMBER(9),
TOTAL NUMBER(12,2),
PREVPAY NUMBER(12,2),
PAY NUMBER(12,2),
STAFFID NUMBER(9),
PROCESSDATE DATE,
PAYNO CHAR(12),
TYPE CHAR(2) DEFAULT '0',
PAYMENTMETHOD CHAR(1) DEFAULT '0',
PAYMENTMETHODID VARCHAR2(20),
BANKACCOUNT VARCHAR2(32),
PAYMENTID NUMBER(9),
STATUS CHAR(1) DEFAULT '0',
MEMO VARCHAR2(255),
SERVICEID NUMBER(9),
CURRENTDEPOSITID NUMBER(9),
SHOULDPROCESSDATE DATE DEFAULT sysdate,
ORIGINALEXPIREDATE DATE,
ORIGINALCANCELDATE DATE,
EXPIREDATE DATE,
CANCELDATE DATE,
DEPOSITTYPE CHAR(1)
)
TABLESPACE USER
PCTUSED 95
PCTFREE 5
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 7312K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCACHE
NOPARALLEL;
SQL>CREATE INDEX USER.I_PAY_STAFFID ON USER.PAY
(STAFFID)
NOLOGGING
TABLESPACE USER
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1936K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL>CREATE UNIQUE INDEX USER.PK_PAY_ID ON USER.PAY
(PAYID)
NOLOGGING
TABLESPACE USER
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1120K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL>ALTER TABLE USER.PAY ADD (
FOREIGN KEY (STAFFID)
REFERENCES USER.STAFF (STAFFID));
SQL>ALTER TABLE USER.DEPOSITCLASSIFY ADD
CONSTRAINT FK_DEPOSITCLASSIFY2
FOREIGN KEY (PAYID)
REFERENCES USER.PAY (PAYID) ;
SQL>ALTER TABLE USER.DEPOSITCREDITLOG ADD
CONSTRAINT FK_DEPOSITCREDITLOG2
FOREIGN KEY (PAYID)
REFERENCES USER.PAY (PAYID) ;
SQL>ALTER FUNCTION "USER"."GENERATEPAYNO" COMPILE ;
SQL>ALTER PROCEDURE "USER"."ENGENDERPRVPAY" COMPILE ;
SQL>ALTER PROCEDURE "USER"."ISAP_ENGENDERPRVPAY" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADDCREDITDEPOSIT" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADDDEPOSITWITHOUTCARD" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADJUSTLWDEPOSIT" COMPILE ;
……
然后將導(dǎo)出的表PAY_X的dmp文件導(dǎo)入一個(gè)臨時(shí)的數(shù)據(jù)庫(kù)中，然后在臨時(shí)數(shù)據(jù)庫(kù)中將其表名重新命名為PAY，再按表模式將其導(dǎo)出。
imp USER/USER file= PAY_x.dmp tables=PAY ignore=y
SQL>rename PAY_X to PAY;
$ exp USER/USER file=PAY.dmp tables=PAY
最后將這個(gè)dmp文件導(dǎo)入正式的生產(chǎn)數(shù)據(jù)庫(kù)中即可。
以上的過(guò)程在重建好PAY表后整個(gè)應(yīng)用就恢復(fù)正常了，而重命名表后重建表的時(shí)間是非常之短的，我測(cè)試的時(shí)間大概是在幾分鐘之內(nèi)就可以做完了，新數(shù)據(jù)就可以插入表了，剩下的工作就是將舊的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)，這個(gè)工作的時(shí)間要求上就沒(méi)有那么高了，因?yàn)閼?yīng)用已經(jīng)正常了，一般來(lái)說(shuō)利用晚上業(yè)務(wù)不忙的時(shí)候都可以把一張表的數(shù)據(jù)導(dǎo)入完成的。
以上的六種清除行遷移的方法各有各自的優(yōu)缺點(diǎn)，分別適用于不同的情況下使用，利用以上的幾種清除行遷移的方法基本上就能完全清除掉系統(tǒng)中的存在的行遷移了，當(dāng)然，具體的生產(chǎn)環(huán)境中還需要具體問(wèn)題具體分析的，針對(duì)不同類(lèi)型的系統(tǒng)，系統(tǒng)中不同特點(diǎn)的表采用不同的清除方法，盡量的減少停數(shù)據(jù)庫(kù)的時(shí)間，以保證應(yīng)用的不間斷穩(wěn)定運(yùn)行。
圖一：Oracle Block結(jié)構(gòu)圖
由上圖我們可以看出，一個(gè)Oracle block由三個(gè)部分組成，分別是數(shù)據(jù)塊頭、自由空間、實(shí)際數(shù)據(jù)三部份組成。
數(shù)據(jù)塊頭：主要包含有數(shù)據(jù)塊地址的一些基本信息和段的類(lèi)型，以及表和包含有數(shù)據(jù)的實(shí)際行的地址。
自由空間：是指可以為以后的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個(gè)參數(shù)影響。
實(shí)際數(shù)據(jù)：是指在行內(nèi)存儲(chǔ)的實(shí)際數(shù)據(jù)。
當(dāng)創(chuàng)建或者更改任何表和索引的時(shí)候，Oracle在空間控制方面使用兩個(gè)存儲(chǔ)參數(shù)：
PCTFREE:為將來(lái)更新已經(jīng)存在的數(shù)據(jù)預(yù)留空間的百分比。
PCTUSED:用于為插入一新行數(shù)據(jù)的最小空間的百分比。這個(gè)值決定了塊的可用狀態(tài)。可用的塊時(shí)可以執(zhí)行插入的塊，不可用狀態(tài)的塊只能執(zhí)行刪除和修改，可用狀態(tài)的塊被放在freelist中。
當(dāng)表中一行的數(shù)據(jù)不能在一個(gè)數(shù)據(jù)block中放入的時(shí)候，這個(gè)時(shí)候就會(huì)發(fā)生兩種情況，一種是行鏈接，另外一種就是行遷移了。
行鏈接產(chǎn)生在第一次插入數(shù)據(jù)的時(shí)候假如一個(gè)block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個(gè)或者多個(gè)在這個(gè)段中保留的block存儲(chǔ)這一行記錄，行鏈接比較輕易發(fā)生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數(shù)據(jù)類(lèi)型的字段，這種時(shí)候行鏈接是不可避免的會(huì)產(chǎn)生的。
當(dāng)一行記錄初始插入的時(shí)候事可以存儲(chǔ)在一個(gè)block中的，由于更新操作導(dǎo)致行長(zhǎng)增加了，而block的自由空間已經(jīng)完全滿了，這個(gè)時(shí)候就產(chǎn)生了行遷移。在這種情況下，Oracle將會(huì)遷移整行數(shù)據(jù)到一個(gè)新的block中（假設(shè)一個(gè)block中可以存儲(chǔ)下整行數(shù)據(jù)），Oracle會(huì)保留被遷移行的原始指針指向新的存放行數(shù)據(jù)的block，這就意味著被遷移行的ROW ID是不會(huì)改變的。
當(dāng)發(fā)生了行遷移或者行鏈接，對(duì)這行數(shù)據(jù)操作的性能就會(huì)降低，因?yàn)镺racle必須要掃描更多的block來(lái)獲得這行的信息。
下面舉例來(lái)具體說(shuō)明行遷移/行鏈接的產(chǎn)生過(guò)程。
先創(chuàng)建一個(gè)pctfree為20和pctused為50的測(cè)試表：
create table test（
col1 char(20),
col2 number)
storage (
pctfree 20
pctused 50);
當(dāng)插入一條記錄的時(shí)候，Oracle會(huì)在free list中先去尋找一個(gè)自由的塊，并且將數(shù)據(jù)插入到這個(gè)自由塊中。而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間達(dá)到pctfree的值，此塊就會(huì)從free list中移走，而當(dāng)此塊中的使用空間低于pctused的時(shí)候，此塊又被重新放到free list中。
Oracle使用free list機(jī)制可以大大的提高性能，對(duì)于每次的插入操作，Oracle只需要查找free list就可以了，而不是去查找所有的block來(lái)尋找自由空間。
假設(shè)第一次插入數(shù)據(jù)使用的一個(gè)空的block，如下圖所示：
圖二：Oracle空的block結(jié)構(gòu)圖
假設(shè)插入第一條記錄的時(shí)候占用一個(gè)block的10%的空間（除去block頭占去的大小），剩余的自由空間90%大于pctfree20%，因此這個(gè)block還將繼續(xù)為下次的插入操作提供空間。
圖三：插入10%后的Oracle block結(jié)構(gòu)圖
再連續(xù)插入七條記錄，使block的剩余自由空間剩下20%，此時(shí)，這個(gè)block將要從free list中移走，假如再插入記錄，Oracle將再free list中尋找下一個(gè)空余的block去存放后來(lái)插入的數(shù)據(jù)。
圖四：插入80%后的Oracle block結(jié)構(gòu)圖
此時(shí)假如去更新第一條插入的記錄，使其行長(zhǎng)增加15%,Oracle將會(huì)使用這個(gè)block中剩余的20%的自由空間來(lái)存放此行數(shù)據(jù)，假如再更新第二條記錄，同樣的使其行長(zhǎng)增加15%,而此block中只剩下5%的自由空間，不夠存放更新的第二條記錄，于是Oracle會(huì)在free list中尋找一個(gè)有自由空間（10%+15%）的block來(lái)存放這行記錄的block去存儲(chǔ)，在原來(lái)的block中保存了指向新的block的指針，原來(lái)這行記錄的ROW ID保持不變，這個(gè)時(shí)候就產(chǎn)生了行遷移。
而當(dāng)我們插入一條新紀(jì)錄的時(shí)候，假如一個(gè)blcok不足以存放下這條記錄，Oracle就會(huì)尋找一定數(shù)量的block一起來(lái)容納這條新的記錄，這個(gè)時(shí)候就產(chǎn)生了行鏈接，行鏈接主要產(chǎn)生在LOB、CLOB、BLOB和大的VA行鏈接HAR2數(shù)據(jù)類(lèi)型上。
具體我們通過(guò)下面的一個(gè)試驗(yàn)來(lái)查看行鏈接和行遷移是如何產(chǎn)生并在數(shù)據(jù)文件中體現(xiàn)出來(lái)的。
先查看ALLAN這個(gè)表空間的數(shù)據(jù)文件號(hào)，為了便于測(cè)試，我只建立了一個(gè)數(shù)據(jù)文件。
SQL> select file_id from dba_data_files where tablespace_name='ALLAN';
FILE_ID
----------
23
創(chuàng)建一個(gè)測(cè)試表test：
SQL> create table test ( x int primary key, a char(2000), b char(2000), c char(2000), d char(2000), e char(2000) ) tablespace allan;
Table created.
因?yàn)槲业臄?shù)據(jù)庫(kù)的db_block_size是8K，所以我創(chuàng)建的表有五個(gè)字段，每個(gè)占2000個(gè)字節(jié)，這樣一行記錄大約10K,就能超過(guò)一個(gè)block的大小了。
然后插入一行記錄，只有一個(gè)字段的：
SQL> insert into test(x) values (1);
1 row created.
SQL> commit;
Commit complete.
查找這行記錄所在的block，并dump出來(lái)：
SQL> select dbms_rowid.rowid_block_number(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
34
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下查看trace文件的內(nèi)容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
對(duì)其中的一些信息做一些解釋?zhuān)?BR>
Fb:H是指行記錄的頭，L是指行記錄的最后一列，F(xiàn)是指行記錄的第一列。
Cc：列的數(shù)量
Nrid：對(duì)于行鏈接或者行遷移來(lái)說(shuō)的下一個(gè)row id的值
由上面的dump信息我們可以看出來(lái)當(dāng)前表test是沒(méi)有行鏈接或者行遷移的。
然后更新test表,并重新dump出來(lái)：
SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;
1 row updated.
SQL> commit;
Commit complete.
此時(shí)應(yīng)該有行遷移/行鏈接產(chǎn)生了。
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下查看trace文件的內(nèi)容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f3
0x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442b
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x178a
avsp=0x177c
tosp=0x177c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x178a
block_row_dump:
tab 0, row 0, @0x178a
tl: 2064 fb: --H-F--N lb: 0x2 cc: 3
nrid: 0x05c00023.0
col 0: [ 2] c1 02
col 1: [2000]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
…………
col 2: [48]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
我們不難看出，nrid出現(xiàn)了值，指向了下一個(gè)row id,證實(shí)剛剛的update操作使這行記錄產(chǎn)生了行鏈接或者行遷移了。
二、行遷移/行鏈接的檢測(cè)
通過(guò)前面的介紹我們知道，行鏈接主要是由于數(shù)據(jù)庫(kù)的db_block_size不夠大，對(duì)于一些大的字段沒(méi)法在一個(gè)block中存儲(chǔ)下，從而產(chǎn)生了行鏈接。對(duì)于行鏈接我們除了增大db_block_size之外沒(méi)有別的任何辦法去避免，但是因?yàn)閿?shù)據(jù)庫(kù)建立后db_block_size是不可改變的（在9i之前），對(duì)于Oracle9i的數(shù)據(jù)庫(kù)我們可以對(duì)不同的表空間指定不同的db_block_size,因此行鏈接的產(chǎn)生幾乎是不可避免的，也沒(méi)有太多可以調(diào)整的地方。行遷移則主要是由于更新表的時(shí)候，由于表的pctfree參數(shù)設(shè)置太小，導(dǎo)致block中沒(méi)有足夠的空間去容納更新后的記錄，從而產(chǎn)生了行遷移。對(duì)于行遷移來(lái)說(shuō)就非常有調(diào)整的必要了，因?yàn)檫@個(gè)是可以調(diào)整和控制清除的。
如何檢測(cè)數(shù)據(jù)庫(kù)中存在有了行遷移和行鏈接呢？我們可以利用Oracle數(shù)據(jù)庫(kù)自身提供的腳本utlchain.sql（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表，然后利用ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個(gè)分析表，將分析的結(jié)果存入chained_rows表中。從utlchain.sql腳本中我們看到chained_rows的建表腳本，對(duì)于分區(qū)表，cluster表都是適用的。然后可以使用拼湊語(yǔ)句的辦法生成分析所需要的表的腳本，并執(zhí)行腳本將具體的分析數(shù)據(jù)放入Chained_rows表中，例如下面是分析一個(gè)用戶下所有表的腳本：
SPOOL list_migation_rows.sql
SET ECHO OFF
SET HEADING OFF
SELECT 'ANALYZE TABLE ' table_name ' LIST CHAINED ROWS INTO chained_rows;' FROM user_tables;
SPOOL OFF
然后查詢chained_rows表，可以具體查看某張表上有多少的行鏈接和行遷移。
SELECT table_name, count(*) from chained_rows GROUP BY table_name;
當(dāng)然，也可以查詢v$sysstat視圖中的’table fetch continued row’列得到當(dāng)前的行鏈接和行遷移數(shù)量。
SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
可以使用如下的腳本來(lái)直接查找存在有行鏈接和行遷移的表，自動(dòng)完成所有的分析和統(tǒng)計(jì)。
accept owner prompt " Enter the schema name to check for Row Chaining (RETURN for All): "
prompt
prompt
accept table prompt " Enter the table name to check (RETURN for All tables owned by &owner): "
prompt
prompt
set head off serverout on term on feed off veri off echo off
!clear
prompt
declare
v_owner varchar2(30);
v_table varchar2(30);
v_chains number;
v_rows number;
v_count number := 0;
sql_stmt varchar2(100);
dynamicCursor INTEGER;
dummy INTEGER;
cursor chains is
select count(*) from chained_rows;
cursor analyze is
select owner, table_name
from sys.dba_tables
where owner like upper('%&owner%')
and table_name like upper('%&table%')
order by table_name;
begin
dbms_output.enable(64000);
open analyze;
fetch analyze into v_owner, v_table;
while analyze%FOUND loop
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'analyze table 'v_owner'.'v_table' list chained rows into chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
open chains;
fetch chains into v_chains;
if (v_chains != 0) then
if (v_count = 0) then
dbms_output.put_line(CHR(9)CHR(9)CHR(9)'<<<<< Chained Rows Found >>>>>');
v_count := 1;
end if;
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'Select count(*) v_rows'' From 'v_owner'.'v_table;
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);
dummy := dbms_sql.execute(dynamicCursor);
dummy := dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner'.'v_table);
dbms_output.put_line(CHR(9)'---> Has 'v_chains' Chained Rows and 'v_rows' Num_Rows in it!');
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'truncate table chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains := 0;
end if;
close chains;
fetch analyze into v_owner, v_table;
end loop;
if (v_count = 0) then
dbms_output.put_line('No Chained Rows found in the 'v_owner' owned Tables!');
end if;
close analyze;
end;
/
set feed on head on
prompt
三、行遷移和行鏈接的清除
由于對(duì)于行鏈接來(lái)說(shuō)只能增大db_block_size來(lái)清除，而db_block_size在創(chuàng)建了數(shù)據(jù)庫(kù)后又是不能改變了的，所以這里對(duì)行鏈接的清除不做過(guò)多的敘述了，主要是針對(duì)行遷移來(lái)談?wù)勗趯?shí)際的生產(chǎn)系統(tǒng)中如何去清除。
對(duì)于行遷移的清除，一般來(lái)說(shuō)分為兩個(gè)步驟：第一步，控制住行遷移的增長(zhǎng)，使其不在增多；第二步，清除掉以前存在的行遷移。
眾所周知，行遷移產(chǎn)生的主要原因是因?yàn)楸砩系膒ctfree參數(shù)設(shè)置過(guò)小導(dǎo)致的，而要實(shí)現(xiàn)第一步控制住行遷移的增長(zhǎng)，就必須設(shè)置好一個(gè)正確合適的pctfree參數(shù)，否則即使清除了當(dāng)前的行遷移后馬上又會(huì)產(chǎn)生很多新的行遷移。當(dāng)然，這個(gè)參數(shù)也不是越大越好的，假如pctfree設(shè)置的過(guò)大，會(huì)導(dǎo)致數(shù)據(jù)塊的利用率低，造成空間的大量浪費(fèi)，因此必須設(shè)置一個(gè)合理的pctfree參數(shù)。如何去確定一個(gè)表上合理的pctfree參數(shù)呢，一般來(lái)說(shuō)有兩種方法。
第一種是定量的的設(shè)定方法，就是利用公式來(lái)設(shè)定pctfree的大小。先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來(lái)分析要修改pctfree的表,然后查看user_tables中的AVG_ROW_LEN列值，得到一個(gè)平均行長(zhǎng)AVG_ROW_LEN1，然后大量的對(duì)表操作之后，再次使用上述命令分析表，得到第二個(gè)平均行長(zhǎng)AVG_ROW_LEN2，然后運(yùn)用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）得出的結(jié)果就是定量計(jì)算出來(lái)的一個(gè)合適的pctfree的值。這種方法因?yàn)槭嵌坑?jì)算出來(lái)的，可能不一定會(huì)很準(zhǔn)確，而且因?yàn)橐治霰?，所以?duì)于使用RBO執(zhí)行計(jì)劃的系統(tǒng)不是很適用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，則平均修改量為 10，PCTFREE 應(yīng)調(diào)整為 100 * 10 /（10 + 60）= 16.7% 。
第二種是差分微調(diào)的方法，先查詢到當(dāng)前表的pctfree的值，然后監(jiān)控和調(diào)整pctfree參數(shù)，每次增加一點(diǎn)pctfree的大小，每次增加的比例不要超過(guò)5個(gè)百分點(diǎn)，然后使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行鏈接的增長(zhǎng)情況，對(duì)于不同的表采取不同的增長(zhǎng)比例，對(duì)于行遷移增長(zhǎng)的比較快的表pctfree值就增加的多點(diǎn)，對(duì)于增長(zhǎng)慢的表就增加的少點(diǎn)，直到表的行遷移基本保持不增長(zhǎng)了為止。但是注重不要把pctfree調(diào)的過(guò)大，一般在40%以下就可以了，否則會(huì)造成空間的很大浪費(fèi)和增加數(shù)據(jù)庫(kù)訪問(wèn)的IO。
使用上述的方法控制住了當(dāng)前表的行遷移的增長(zhǎng)之后，就可以開(kāi)始清除之前表上存在的行遷移了。是否清除掉行遷移，關(guān)系到系統(tǒng)的性能是否能夠有很大的提高。因此，對(duì)于以前存在的行遷移是一定而且必須要清除掉的。清除掉已經(jīng)存在的行遷移有很多方法，但是并不是所有的方法都能適用所有的情況，例如表中的記錄數(shù)多少，表上的關(guān)聯(lián)多少、表上行遷移的數(shù)量多少等等這些因素都會(huì)是成為制約你使用什么方法清除的條件，因此，根據(jù)表的特點(diǎn)和具體情況的不同我們應(yīng)該采用不同的方法去清除行遷移。下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況。
方法一：傳統(tǒng)的清除行遷移的方法
具體步驟如下：
1． 執(zhí)行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql腳本創(chuàng)建chained_rows表。
@$ORACLE_HOME/rdbms/admin/utlchain.sql
2． 將存在有行遷移的表（用table_name代替）中的產(chǎn)生行遷移的行的rowid放入到chained_rows表中。
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;
3． 將表中的行遷移的row id放入臨時(shí)表中保存。
CREATE TABLE table_name_temp AS
SELECT * FROM table_name
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'table_name');
4． 刪除原來(lái)表中存在的行遷移的記錄行。
DELETE table_name
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'table_name');
5． 從臨時(shí)表中取出并重新插入那些被刪除了的數(shù)據(jù)到原來(lái)的表中，并刪除臨時(shí)表。
INSERT INTO table_name SELECT * FROM table_name_temp;
DROP TABLE table_name_temp;
對(duì)于這種傳統(tǒng)的清除RM的方法，優(yōu)點(diǎn)是執(zhí)行起來(lái)過(guò)程比較簡(jiǎn)單，輕易實(shí)現(xiàn)。但是這種算法的缺陷是沒(méi)有考慮到表關(guān)聯(lián)的情況，在大多數(shù)數(shù)據(jù)庫(kù)中很多表都是和別的表之間有表關(guān)聯(lián)的，有外鍵的限制，這樣就造成在步驟3中根本無(wú)法delete掉存在有行遷移的記錄行，所以這種方法能夠適用的表的范圍是有限的，只能適用于表上無(wú)任何外鍵關(guān)聯(lián)的表。由于這種方法在插入和刪除數(shù)據(jù)的時(shí)候都沒(méi)有disable掉索引，這樣導(dǎo)致主要消耗時(shí)間是在刪除和插入時(shí)維持索引樹(shù)的均衡上了，這個(gè)對(duì)于假如記錄數(shù)不多的情況時(shí)間上還比較短，但是假如對(duì)于記錄數(shù)很多的表這個(gè)所消耗的時(shí)間就不是能夠接受的了。顯然，這種方法在處理大數(shù)據(jù)量的表的時(shí)候顯然是不可取的。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Table created.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows；
TABLE_NAME COUNT(*)
------------------------------ ----------
CUSTOMER 21306
1 rows selected.
查看在CUSTOMER表上存在的限制:
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_CUSTOMER1 P CUSTOMER
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_CUSTOMER1';
no rows selected
SQL> CREATE TABLE CUSTOMER_temp AS
SELECT * FROM CUSTOMER WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'CUSTOMER');
Table created.
SQL>select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> DELETE CUSTOMER WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'CUSTOMER');
21306 rows deleted.
SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;
21306 rows created.
SQL> DROP TABLE CUSTOMER_temp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> truncate table chained_rows;
Table truncated.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
以上整個(gè)清除兩萬(wàn)多行的行遷移過(guò)程在三分鐘左右，而且全部都在聯(lián)機(jī)的狀態(tài)下完成，基本上不會(huì)對(duì)業(yè)務(wù)有什么影響，唯一就是在要清除行遷移的表上不能有對(duì)外鍵的限制，否則就不能采用這個(gè)方法去清除了。
方法二：改進(jìn)了的傳統(tǒng)清除行遷移的方法
1． 執(zhí)行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql腳本創(chuàng)建chained_rows表。
2． 禁用所有其它表上關(guān)聯(lián)到此表上的所有限制。
3． 將表中的行遷移的row id放入臨時(shí)表中保存。
4． 刪除原來(lái)表中存在的行遷移的記錄行。
5． 從臨時(shí)表中取出并重新插入那些被刪除了的數(shù)據(jù)到原來(lái)的表中，并刪除臨時(shí)表。
6． 啟用所有其它表上關(guān)聯(lián)到此表上的所有限制。
這種算法是對(duì)傳統(tǒng)算法的一種改進(jìn)，對(duì)于使用這種算法來(lái)清除行遷移，考慮到了表之間的關(guān)聯(lián)，還可以靈活的利用的TOAD工具生成的表關(guān)聯(lián)信息，是一種比較適合于清除行遷移的一種方法。但是因?yàn)槭褂眠@種方法后來(lái)需要重建索引，假如記錄數(shù)很大，比如說(shuō)上千萬(wàn)條以上的記錄的表，就不是很合適了，因?yàn)檫@個(gè)重建索引的時(shí)間會(huì)很長(zhǎng)，是線性時(shí)間復(fù)雜度的，而重建索引是會(huì)導(dǎo)致索引所在的表被鎖定的，從而導(dǎo)致插入不了新的記錄，重建索引的時(shí)間太長(zhǎng)導(dǎo)致記錄長(zhǎng)時(shí)間插入不了是會(huì)嚴(yán)重影響應(yīng)用的，甚至導(dǎo)致數(shù)據(jù)的丟失，因此這個(gè)是使用這個(gè)方法前必須要考慮到的一個(gè)重要因素；對(duì)于8i以上的版本可以使用online的方法來(lái)重建索引，這樣不會(huì)導(dǎo)致鎖表，但是會(huì)有額外更多的開(kāi)銷(xiāo)，時(shí)間會(huì)很長(zhǎng)。再者，因?yàn)檫@種方法在插入記錄和刪除記錄都是帶著索引的，假如表上的行遷移比較多，這樣耗時(shí)間會(huì)比較長(zhǎng)，而且占用資源也會(huì)比較大，因此只適用于表上行遷移存在的比較少的表?？偟膩?lái)說(shuō)，這種方法對(duì)于表記錄太多或者是表上的行遷移太多的情況都不是很適用，比較適合表記錄少和表上行遷移都不太多的情況。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>select index_name,index_type,table_name from user_indexes where table_name='TERMINAL';
INDEX_NAME INDEX_TYPE TABLE_NAME
-----------------------------------------------------------------
INDEX_TERMINAL_TERMINALCODE NORMAL TERMINAL
I_TERMINAL_ID_TYPE NORMAL TERMINAL
I_TERMINAL_OT_OID NORMAL TERMINAL
PK_TERMINAL_ID NORMAL TERMINAL
UI_TERMINAL_GOODIS_SSN NORMAL TERMINAL
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_TERMINAL_ID';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C003200 R CONN
SQL>alter table CONN disable constraint SYS_C003200;
Table altered.
SQL>CREATE TABLE TERMINAL_temp AS
SELECT * FROM TERMINAL
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'TERMINAL');
Table created.
SQL>select count(*) from TERMINAL_temp;
COUNT(*)
----------
8302
SQL>DELETE TERMINAL
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'TERMINAL');
8302 rows deleted.
SQL>INSERT INTO TERMINAL SELECT * FROM TERMINAL_temp;
8302 rows created.
SQL>alter table CONN disable constraint SYS_C003200;
Table altered.
SQL>select count(*) from terminal;
COUNT(*)
----------
647799
SQL>truncate table chained_rows;
Table truncated.
SQL>ANALYZE TABLE TERMINAL LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>select count(*) from chained_rows;
COUNT(*)
----------
0
從上面過(guò)程中可以看出，對(duì)TERMINAL這張表的行遷移清除耗時(shí)總共不到五分鐘的時(shí)間，總體來(lái)說(shuō)還是比較快的。從我在生產(chǎn)數(shù)據(jù)庫(kù)中清除行遷移的經(jīng)驗(yàn)來(lái)說(shuō)，這種方法基本適用于大部分存在有行遷移的表。
方法三：使用TOAD工具清除行遷移的方法
1． 備份要清除RM的表。
RENAME table_name TO table_name_temp;
2． Drop 所有其它表上關(guān)聯(lián)到table_name的外鍵限制。
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in (SELECT CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME='table_name' AND CONSTRAINT_TYPE=’P’);
ALTER TABLE table_name DROP CONSTRAINT XXXX;（XXXX為上述的查詢結(jié)果）
3． 重建1中被rename的表。
CREATE TABLE table_name AS SELECT * FROM table_name_temp WHERE 0 = 1;
4． 重建表中原來(lái)的數(shù)據(jù)。
INSERT /*+ APPEND */ INTO table_name SELECT * FROM table_name_temp;
5． 刪除在table_name_temp上的索引和關(guān)聯(lián)其他表的外鍵。
6． 在table_name上建立和原來(lái)一樣的索引、主鍵和所有的外鍵限制。
7． 重新編譯相關(guān)的存儲(chǔ)過(guò)程、函數(shù)和包。
8． 刪除表table_name_temp。
對(duì)于使用這種方法來(lái)清除行遷移，全部的代碼都是可以由TOAD工具來(lái)生成的。由于此方法把表上的關(guān)聯(lián)考慮進(jìn)去了，也是一種比較的全面的考慮的一種清除方法，而且在清除過(guò)程中重建了表和索引，對(duì)于數(shù)據(jù)庫(kù)的存儲(chǔ)和性能上都有提高。因?yàn)檫@種方法一開(kāi)始是rename表為臨時(shí)表，然后重建一個(gè)新表出來(lái)的，因此需要兩倍的表的空間，因此在操作之前一定要檢查要清除的表所在的表空間的free空間是否足夠；但是也有一定的缺陷，因?yàn)樵谛卤碇兄匦虏迦朐瓉?lái)的數(shù)據(jù)后需要重建索引和限制，因此在時(shí)間和磁盤(pán)的空間上都有比較大的開(kāi)銷(xiāo)，而且對(duì)于前臺(tái)的應(yīng)用可能會(huì)有一段時(shí)間的中斷，當(dāng)然，這個(gè)中斷時(shí)間就主要是消耗在重建索引和重建限制上了，而時(shí)間的長(zhǎng)短跟需要重建索引和限制的多少以及表的記錄多少等等因素都有關(guān)系。使用這種方法對(duì)于7*24小時(shí)要求的系統(tǒng)上清除行遷移不是很合適，因?yàn)槭褂眠@種方法會(huì)導(dǎo)致系統(tǒng)可能有一段時(shí)間的停機(jī)，假如系統(tǒng)的實(shí)時(shí)性比較高，這種方法就不是很適用了。
方法四：使用EXP/IMP工具清除行遷移的方法
1． 使用EXP導(dǎo)出存在有行遷移的表。
2． 然后TRUNCATE原來(lái)的表。
3． IMP開(kāi)始導(dǎo)出的表。
4． 重建表上所有的索引。（可選）
使用這種方法可以不用重建索引，省去了這部分時(shí)間，但是完成之后索引的使用效率不會(huì)很高，最好是在以后逐步的在線重建索引，這樣是可以不需要中斷業(yè)務(wù)的。但是需要考慮的是IMP的時(shí)候會(huì)比較慢，而且會(huì)占用比較大的IO，應(yīng)該選擇在應(yīng)用不是很繁忙的時(shí)候做這項(xiàng)工作，否則會(huì)對(duì)應(yīng)用的正常運(yùn)行產(chǎn)生較大的影響。對(duì)于這種方法還存在有一個(gè)比較大的弊端，就是在EXP表的時(shí)候要保證該表是沒(méi)有數(shù)據(jù)的更新或者是只讀狀態(tài)的，不能對(duì)表有插入或者更新操作，否則會(huì)導(dǎo)致數(shù)據(jù)的丟失。
SQL> select count(*) from test;
COUNT(*)
----------
169344
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
3294
$ exp allan/allan file=test.dmp tables=test
Export: Release 9.2.0.3.0 - Production on Sun Jun 6 13:50:08 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST 169344 rows exported
Export terminated successfully without warnings.
$ sqlplus allan/allan
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Jun 6 13:50:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> truncate table test;
Table truncated.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
$ imp allan/allan file=test.dmp full=y ignore=y buffer=5000000
Import: Release 9.2.0.3.0 - Production on Sun Jun 6 13:51:24 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ALLAN's objects into ALLAN
. . importing table "TEST" 169344 rows imported
Import terminated successfully without warnings.
$ sqlplus allan/allan
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Jun 6 13:52:53 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> select count(*) from test;
COUNT(*)
----------
169344
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
OBJ_INDEX
SQL> alter index OBJ_INDEX rebuild online;
Index altered.
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
方法五：使用MOVE命令來(lái)清除行遷移的方法
1． 查看要清除行遷移的表所在的表空間。
Select table_name,tablespace_name from user_tables where table_name='table_name’;
2． 查看要清除行遷移的表上的具體索引。
select index_name,table_name from user_indexes where table_name＝‘table_name’;
3． Move要清除RM的表到指定的表空間中去。
alter table table_name move tablespace tablespace_name;
4． 重建表上的所有索引。
alter index index_name rebuild;
這種方法適用于8i及其以上的數(shù)據(jù)庫(kù)版本，主要是利用數(shù)據(jù)庫(kù)的一個(gè)MOVE命令來(lái)實(shí)現(xiàn)行遷移的清除的，MOVE命令的實(shí)質(zhì)其實(shí)就是INSERT … SELECT的一個(gè)過(guò)程，在MOVE表的過(guò)程中是需要兩倍的原來(lái)的表大小的，因?yàn)橹虚g過(guò)程是要保留原來(lái)的舊表的，新表創(chuàng)建完成后舊表就被刪除并釋放空間了。MOVE的時(shí)候要注重后面一定要加上表空間參數(shù)，所以必須要先知道表所在的表空間；因?yàn)镸OVE表之后需要重建索引，所以之前要確定表上的所有的索引。
這種方法對(duì)于表記錄數(shù)很大或者表上索引太多的情況不太適用，因?yàn)楸旧淼腗OVE就會(huì)很慢， 而且MOVE表的時(shí)候會(huì)要鎖定表，時(shí)間長(zhǎng)了會(huì)導(dǎo)致對(duì)表的其他操作出現(xiàn)問(wèn)題，導(dǎo)致數(shù)據(jù)插入不了丟失數(shù)據(jù)；MOVE表后還要重建索引，索引太多了的話重建的時(shí)間也會(huì)太長(zhǎng)；再者，這個(gè)方法也比較消耗資源，因此強(qiáng)烈建議在業(yè)務(wù)不繁忙的時(shí)候再執(zhí)行。
以下是一個(gè)具體在生產(chǎn)數(shù)據(jù)庫(kù)上清除行遷移的例子，在這之前已經(jīng)調(diào)整過(guò)表的pctfree參數(shù)至一個(gè)合適的值了：
SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows;
COUNT(*)
----------
9145
SQL>select table_name,tablespace_name from user_tables where table_name='SERVICE';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SERVICE DATA
SQL>select index_name,table_name from user_indexes where table_name='SERVICE';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_SERVICE_ACCOUNTNUM SERVICE
I_SERVICE_DATEACTIVATED SERVICE
I_SERVICE_SC_S SERVICE
I_SERVICE_SERVICECODE SERVICE
PK_SERVICE_SID SERVICE
SQL>select count(*) from SERVICE;
COUNT(*)
----------
518718
SQL>alter table SERVICE move tablespace DATA;
Table altered.
SQL>alter index I_SERVICE_ACCOUNTNUM rebuild;
Index altered.
SQL>alter index I_SERVICE_DATEACTIVATED rebuild;
Index altered.
SQL>alter index I_SERVICE_SC_S rebuild;
Index altered.
SQL>alter index I_SERVICE_SERVICECODE rebuild;
Index altered.
SQL>alter index PK_SERVICE_SID rebuild;
Index altered.
SQL>truncate table chained_rows;
Table truncated.
SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows;
COUNT(*)
----------
0
利用MOVE命令來(lái)清除行遷移，執(zhí)行的命令都相對(duì)比較的簡(jiǎn)單，上面的例子中清除表SERVCIE中的行遷移的時(shí)間大概在五分鐘左右，其中move命令執(zhí)行的時(shí)間為不到兩分鐘，也就是鎖表的時(shí)間大概是不到兩分鐘，對(duì)于大多數(shù)的應(yīng)用來(lái)說(shuō)一般問(wèn)題都是不大的，放在系統(tǒng)閑的時(shí)候執(zhí)行基本上不會(huì)對(duì)應(yīng)用產(chǎn)生什么太多的影響。
方法六：對(duì)于一些行遷移數(shù)量巨大而且表記錄數(shù)巨大的表的行遷移的清除方法
1． 使用TOAD工具或者別的方法獲取存在有大量行遷移并且表記錄很大的表的重建表的SQL，然后保存為腳本。
2． 使用RENAME命令將原始表重命名為一個(gè)備份表，然后刪除別的表對(duì)原始表上的限制、以及原始表上的外鍵和索引。
3． 利用1中生成的腳本重建原始表，以及表上的限制，外鍵，索引等對(duì)象。
4． 然后按表模式導(dǎo)出2中備份的表，然后導(dǎo)入到另外的一個(gè)臨時(shí)中轉(zhuǎn)的數(shù)據(jù)庫(kù)庫(kù)中，因?yàn)楸淼拿忠呀?jīng)改變，所以導(dǎo)入后需要RENAME表為原來(lái)的名字，然后重新導(dǎo)出，最后再導(dǎo)入到原來(lái)的數(shù)據(jù)庫(kù)中。
這種方法主要是用來(lái)針對(duì)一些數(shù)據(jù)量比較大，并且表上的行遷移也比較多的表的行遷移清除。對(duì)于這些大表的行遷移的清除，正常來(lái)說(shuō)都需要停應(yīng)用一段較長(zhǎng)時(shí)間才能夠清除掉，讓人感覺(jué)比較的頭疼，對(duì)于7*24小時(shí)的應(yīng)用來(lái)說(shuō)，down機(jī)的時(shí)間越長(zhǎng)損失則越大，當(dāng)然是要盡量的減短down機(jī)的時(shí)間。但是因?yàn)楸肀旧肀容^大，不管怎樣做什么操作都是會(huì)比較耗費(fèi)時(shí)間和資源的，但是假如應(yīng)用在某段時(shí)間內(nèi)主要是以插入數(shù)據(jù)為主，更新數(shù)據(jù)和刪除數(shù)據(jù)都很少的，因此可以考慮可以采用這么一種方法：先重命名表，然后重新建立一個(gè)和原來(lái)一樣的表，用來(lái)保證之后的應(yīng)用的數(shù)據(jù)是可以正常插入的，從而使應(yīng)用不用停很久，因?yàn)橹亟ㄒ粋€(gè)沒(méi)有任何數(shù)據(jù)的表結(jié)構(gòu)的過(guò)程是很短暫的，大概需要幾秒鐘的時(shí)間，而重建好表了后就能保證應(yīng)用能夠正常的寫(xiě)入數(shù)據(jù)，從而使應(yīng)用幾乎不用停頓，然后把開(kāi)始重命名的原始表按表模式導(dǎo)出，因?yàn)楸淼拿忠呀?jīng)被改變，因此需要一個(gè)臨時(shí)庫(kù)來(lái)導(dǎo)入這些數(shù)據(jù)，然后重命名回原來(lái)的名字，然后按原來(lái)的表名導(dǎo)出后再重新導(dǎo)入原始數(shù)據(jù)庫(kù)，這樣操作起來(lái)雖然會(huì)比較麻煩，但是卻是一種很有效很實(shí)際的方法，速度也很快，導(dǎo)出后導(dǎo)入，因?yàn)楸旧肀斫Y(jié)構(gòu)已經(jīng)建立好了，不需要其他任何的多的操作，而且最要害的是這種方法所需要的down機(jī)時(shí)間是最短的。
SQL>ALTER TABLE USER.PAY RENAME TO PAY_X ;
然后導(dǎo)出PAY_X表;
$ exp USER/USER file=PAY_X.dmp tables=PAY_X
SQL>ALTER TABLE USER.BATCHPAYMENTDETAIL DROP CONSTRAINT FK_BATCHPAYMENTDETAIL_OPAYID ;
SQL>ALTER TABLE USER.DEPOSITCLASSIFY DROP CONSTRAINT FK_DEPOSITCLASSIFY2 ;
SQL>ALTER TABLE USER.DEPOSITCREDITLOG DROP CONSTRAINT FK_DEPOSITCREDITLOG2 ;
SQL>ALTER TABLE USER.DEPOSIT DROP CONSTRAINT SYS_C003423 ;
SQL>ALTER TABLE USER.PAY_X DROP CONSTRAINT SYS_C003549 ;
SQL>DROP INDEX USER.I_PAY_STAFFID ;
SQL>CREATE TABLE USER.PAY
(
PAYID NUMBER(9),
ACCOUNTNUM NUMBER(9),
TOTAL NUMBER(12,2),
PREVPAY NUMBER(12,2),
PAY NUMBER(12,2),
STAFFID NUMBER(9),
PROCESSDATE DATE,
PAYNO CHAR(12),
TYPE CHAR(2) DEFAULT '0',
PAYMENTMETHOD CHAR(1) DEFAULT '0',
PAYMENTMETHODID VARCHAR2(20),
BANKACCOUNT VARCHAR2(32),
PAYMENTID NUMBER(9),
STATUS CHAR(1) DEFAULT '0',
MEMO VARCHAR2(255),
SERVICEID NUMBER(9),
CURRENTDEPOSITID NUMBER(9),
SHOULDPROCESSDATE DATE DEFAULT sysdate,
ORIGINALEXPIREDATE DATE,
ORIGINALCANCELDATE DATE,
EXPIREDATE DATE,
CANCELDATE DATE,
DEPOSITTYPE CHAR(1)
)
TABLESPACE USER
PCTUSED 95
PCTFREE 5
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 7312K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCACHE
NOPARALLEL;
SQL>CREATE INDEX USER.I_PAY_STAFFID ON USER.PAY
(STAFFID)
NOLOGGING
TABLESPACE USER
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1936K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL>CREATE UNIQUE INDEX USER.PK_PAY_ID ON USER.PAY
(PAYID)
NOLOGGING
TABLESPACE USER
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1120K
NEXT 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL>ALTER TABLE USER.PAY ADD (
FOREIGN KEY (STAFFID)
REFERENCES USER.STAFF (STAFFID));
SQL>ALTER TABLE USER.DEPOSITCLASSIFY ADD
CONSTRAINT FK_DEPOSITCLASSIFY2
FOREIGN KEY (PAYID)
REFERENCES USER.PAY (PAYID) ;
SQL>ALTER TABLE USER.DEPOSITCREDITLOG ADD
CONSTRAINT FK_DEPOSITCREDITLOG2
FOREIGN KEY (PAYID)
REFERENCES USER.PAY (PAYID) ;
SQL>ALTER FUNCTION "USER"."GENERATEPAYNO" COMPILE ;
SQL>ALTER PROCEDURE "USER"."ENGENDERPRVPAY" COMPILE ;
SQL>ALTER PROCEDURE "USER"."ISAP_ENGENDERPRVPAY" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADDCREDITDEPOSIT" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADDDEPOSITWITHOUTCARD" COMPILE ;
SQL>ALTER PROCEDURE "USER"."SPADJUSTLWDEPOSIT" COMPILE ;
……
然后將導(dǎo)出的表PAY_X的dmp文件導(dǎo)入一個(gè)臨時(shí)的數(shù)據(jù)庫(kù)中，然后在臨時(shí)數(shù)據(jù)庫(kù)中將其表名重新命名為PAY，再按表模式將其導(dǎo)出。
imp USER/USER file= PAY_x.dmp tables=PAY ignore=y
SQL>rename PAY_X to PAY;
$ exp USER/USER file=PAY.dmp tables=PAY
最后將這個(gè)dmp文件導(dǎo)入正式的生產(chǎn)數(shù)據(jù)庫(kù)中即可。
以上的過(guò)程在重建好PAY表后整個(gè)應(yīng)用就恢復(fù)正常了，而重命名表后重建表的時(shí)間是非常之短的，我測(cè)試的時(shí)間大概是在幾分鐘之內(nèi)就可以做完了，新數(shù)據(jù)就可以插入表了，剩下的工作就是將舊的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)，這個(gè)工作的時(shí)間要求上就沒(méi)有那么高了，因?yàn)閼?yīng)用已經(jīng)正常了，一般來(lái)說(shuō)利用晚上業(yè)務(wù)不忙的時(shí)候都可以把一張表的數(shù)據(jù)導(dǎo)入完成的。
以上的六種清除行遷移的方法各有各自的優(yōu)缺點(diǎn)，分別適用于不同的情況下使用，利用以上的幾種清除行遷移的方法基本上就能完全清除掉系統(tǒng)中的存在的行遷移了，當(dāng)然，具體的生產(chǎn)環(huán)境中還需要具體問(wèn)題具體分析的，針對(duì)不同類(lèi)型的系統(tǒng)，系統(tǒng)中不同特點(diǎn)的表采用不同的清除方法，盡量的減少停數(shù)據(jù)庫(kù)的時(shí)間，以保證應(yīng)用的不間斷穩(wěn)定運(yùn)行。
