oracle 9i提供了dbms_redefinition包來實現資料庫的表的線上重定義功能。在實際的應用上，我們可以利用這個包來進行：（1）堆表與分割槽之間進行轉換。（2）重建表以減少HWM。10g能shrink，9i如果用move tablespace and rebuild index在move的時候會鎖表，如果想實現線上降低HWM，估計只能用這個了。（3）線上更改表結構，如更改列的前後順序，將column_a，column_b改成column_b，column_a。

下面就來看看這個包的內容：

CREATE OR REPLACE PACKAGE SYS.dbms_redefinition IS
------------
-- OVERVIEW
--
-- This package provides the API to perform. an online, out-of-place
-- redefinition of a table

--- =========
--- CONSTANTS
--- =========
-- Constants for the options_flag parameter of start_redef_table
cons_use_pk CONSTANT BINARY_INTEGER := 1;
cons_use_rowid CONSTANT BINARY_INTEGER := 2;

-- NAME: can_redef_table - check if given table can be re-defined
-- INPUTS: uname - table owner name
-- tname - table name
-- options_flag - flag indicating user options to use
PROCEDURE can_redef_table(uname IN VARCHAR2,
   tname IN VARCHAR2,
   options_flag IN BINARY_INTEGER := 1);

-- NAME: start_redef_table - start the online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
-- col_mapping - select list col mapping
-- options_flag - flag indicating user options to use
PROCEDURE start_redef_table(uname IN VARCHAR2,
   orig_table IN VARCHAR2,
   int_table IN VARCHAR2,
   col_mapping IN VARCHAR2 := NULL,
   options_flag IN BINARY_INTEGER := 1);

-- NAME: finish_redef_table - complete the online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE finish_redef_table(uname IN VARCHAR2,
   orig_table IN VARCHAR2,
   int_table IN VARCHAR2);

-- NAME: abort_redef_table - clean up after errors or abort the
-- online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE abort_redef_table(uname IN VARCHAR2,
   orig_table IN VARCHAR2,
   int_table IN VARCHAR2);

-- NAME: sync_interim_table - synchronize interim table with the original
-- table
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE sync_interim_table(uname IN VARCHAR2,
   orig_table IN VARCHAR2,
   int_table IN VARCHAR2);
END;

需要說明的是，如果採用主鍵作為線上重定義的關聯，options_flag 為1，此為預設值，如果表中沒有主鍵，可以用rowid作為線上重定義的關聯，但是options_flag 要為2;col_mapping為需要對應的欄位，如需要將原表（orig_table ）的id欄位和中間臨時表（int_table）的col_id 欄位對於，則需要col_mapping=>’ID COL_ID’，如果完全對應，col_mapping為null，此為預設值。

下面來看看這個包使用的例子（用PK來同步）：

create table test.xxx as select * from dba_objects;
insert into xxx select * from xxx;
insert into xxx select * from xxx;
（insert 若干次……）
insert into xxx select * from xxx;
commit；
delete from xxx;
insert into xxx select * from dba_objects;
insert into xxx select * from xxx;
insert into xxx select * from xxx;
（insert 若干次……）
insert into xxx select * from xxx;
commit;
update xxx set object_id=rownum;
alert table xxx add CONSTRAINT P_YY PRIMARY KEY (OBJECT_ID);

好，我們現在已經構造了一個xxx表，且由於經常insert和delete，這個表的HWM比較高，其中浪費的block已經比較多。

SQL> analyze table xxx compute statistics;

Table analyzed.

SQL> select TABLE_NAME,HWM,AVG_USED_BLOCKS,
2 GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
3 from
4 (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
5 DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
6 0, 1,
7 ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ('XXX')
13 and SEGMENT_TYPE = 'TABLE'
14 );

TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
------------------------------ ---------- --------------- ---------- ---------
XXX 30652 437 98.57 26-OCT-07

我們看到這個表的HWM有30652個block，而平均使用的block只有437個block，98%是浪費的。

下面進行這個表的線上重定義，首先建立中間臨時表YYY：

SQL> CREATE TABLE "TEST"."YYY"
2 ( "OWNER" VARCHAR2(30),
3 "OBJECT_NAME" VARCHAR2(128),
4 "SUBOBJECT_NAME" VARCHAR2(30),
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(18),
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19),
11 "STATUS" VARCHAR2(7),
12 "TEMPORARY" VARCHAR2(1),
13 "GENERATED" VARCHAR2(1),
14 "SECONDARY" VARCHAR2(1),
15 CONSTRAINT "P_YY" PRIMARY KEY ("OBJECT_ID")
16 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
17 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
18 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
19 TABLESPACE "MSP" ENABLE
20 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
21 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
22 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
23 TABLESPACE "MSP";

Table created.
SQL> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','XXX');

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.START_REDEF_TABLE('TEST','XXX','YYY');

PL/SQL procedure successfully completed.

由於我們採用的是PK做關聯，因此不必使用options_flag=>2。

讓我們來對xxx表經常線上的操作，如update，insert等等的操作：

SQL> update xxx set object_id=rownum+10000;

33722 rows updated.

SQL> commit;

Commit complete.

SQL> insert into xxx select * from dba_objects;
insert into xxx select * from dba_objects
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.P_XX) violated

SQL> update xxx set object_id=rownum+1000000000;

33722 rows updated.

SQL> commit;

Commit complete.

SQL> insert into xxx select * from dba_objects;

34699 rows created.

SQL> commit;

Commit complete.

SQL> delete from xxx where rownum<10000;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from xxx;

COUNT(*)
----------
58422

SQL> select count(*) from yyy;

COUNT(*)
----------
33722

SQL>
SQL>

在這裡，我們看到，當dbms_redefinition.START_REDEF_TABLE開始時候，xxx表和yyy表的記錄數是一樣的，但是當xxx表進行更新的時，yyy表還保持著在start時候的狀態。（我們可以用dbms_redefinition.SYNC_INTERIM_TABLE進行同步，待會會介紹）

我們來看一下此時的xxx表和yyy表的HWM情況：

SQL> analyze table xxx compute statistics;

Table analyzed.

SQL> analyze table yyy compute statistics;

Table analyzed.

SQL> select TABLE_NAME,HWM,AVG_USED_BLOCKS,
2 GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
3 from
4 (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
5 DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
6 0, 1,
7 ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ('XXX')
13 and SEGMENT_TYPE = 'TABLE'
14 );

TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
------------------------------ ---------- --------------- ---------- ---------
XXX 30652 763 97.51 26-OCT-07

SQL> select TABLE_NAME,HWM,AVG_USED_BLOCKS,
2 GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
3 from
4 (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
5 DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
6 0, 1,
7 ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ('YYY')
13 and SEGMENT_TYPE = 'TABLE'
14 );

TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
------------------------------ ---------- --------------- ---------- ---------
YYY 475 437 8 26-OCT-07

我們看到xxx表達還是有98%左右的浪費，而yyy只有8%左右的浪費。

進行一次資料同步（其實是一次物化檢視的重新整理，後面會介紹）：

SQL> exec dbms_redefinition.SYNC_INTERIM_TABLE('TEST','XXX','YYY');

PL/SQL procedure successfully completed.

SQL> select count(*) from xxx;

COUNT(*)
----------
   58422

SQL> select count(*) from yyy;

COUNT(*)
----------
   58422

完成同步後，xxx和yyy保持一致，但是由於是線上系統，xxx表仍然會有變化，我們在這邊做同步的目的是為了在做finish_redef_table能同步較少的資料。

最後，我們結束同步，其內部操作是是將xxx表和yyy表的名稱互換，我們看看結束同步後HWM的變化：

SQL> select TABLE_NAME,HWM,AVG_USED_BLOCKS,
2 GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
3 from
4 (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
5 DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
6 0, 1,
7 ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ('XXX')
13 and SEGMENT_TYPE = 'TABLE'
14 );

TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
------------------------------ ---------- --------------- ---------- ---------
XXX 825 763 7.52 26-OCT-07

SQL> select TABLE_NAME,HWM,AVG_USED_BLOCKS,
2 GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
3 from
4 (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
5 DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
6 0, 1,
7 ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ('YYY')
13 and SEGMENT_TYPE = 'TABLE'
14 );

TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
------------------------------ ---------- --------------- ---------- ---------
YYY 30652 763 97.51 26-OCT-07

在這裡，我們看到xxx表和yyy的HWM的使用率完全倒過來了，也就是說，將xxx表和yyy表的名字進行了互換。（但是表中的原來的PK還跟著原來的表）。
這樣，我們就線上的完成了降低HWM。

同樣的，我們也可以用rowid來進行表的線上重定義（不適合IOT表），操作步驟在這邊就不一步一步解釋了，基本就PK的一樣，見下面的實驗：

test@ORALOCAL(192.168.0.12)> create table xxx as select * from dba_users;

表已建立。

已用時間: 00: 00: 01.11
test@ORALOCAL(192.168.0.12)> desc xxx;
名稱是否為空? 型別
----------------------------------------------------- -------- ------------------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)

test@ORALOCAL(192.168.0.12)> create table yyy as select * from xxx where 1=2;

表已建立。

已用時間: 00: 00: 00.48
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)> create index idx_yyy on yyy(USER_ID);

索引已建立。

已用時間: 00: 00: 00.30
test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','XXX',2);

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.40
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','XXX',2);

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.00

test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','XXX',2);

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.32
test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.START_REDEF_TABLE('TEST','XXX','YYY',null,2);

PL/SQL 過程已成功完成。

已用時間: 00: 01: 20.92
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.SYNC_INTERIM_TABLE('TEST','XXX','YYY');

PL/SQL 過程已成功完成。

已用時間: 00: 00: 02.26
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)>
test@ORALOCAL(192.168.0.12)> exec dbms_redefinition.FINISH_REDEF_TABLE('TEST','XXX','YYY');

PL/SQL 過程已成功完成。

已用時間: 00: 00: 25.47

不過需要注意的時候，用rowid線上重定義，重定義之後的表會生成一個隱藏的M_ROW$$列，我們可以unused後，drop它：

system@ORALOCAL(192.168.0.12)> desc xxx
名稱是否為空? 型別
----------------------------------------------------- -------- ------------------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)

system@ORALOCAL(192.168.0.12)> select table_name,COLUMN_NAME,decode(NULLABLE,'N','NOT NULL','Y',''),
2 DATA_TYPE,HIDDEN_COLUMN from user_tab_cols where table_name='XXX';

TABLE_NAME COLUMN_NAME DECODE(N DATA_TYPE HID
------------------------------ ------------------------------ -------- ---------- ---
XXX USERNAME NOT NULL VARCHAR2 NO
XXX USER_ID NOT NULL NUMBER NO
XXX PASSWORD VARCHAR2 NO
XXX ACCOUNT_STATUS NOT NULL VARCHAR2 NO
XXX LOCK_DATE DATE NO
XXX EXPIRY_DATE DATE NO
XXX DEFAULT_TABLESPACE NOT NULL VARCHAR2 NO
XXX TEMPORARY_TABLESPACE NOT NULL VARCHAR2 NO
XXX CREATED NOT NULL DATE NO
XXX PROFILE NOT NULL VARCHAR2 NO
XXX INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 NO
XXX EXTERNAL_NAME VARCHAR2 NO
XXX M_ROW$$ VARCHAR2 YES

已選擇13行。

system@ORALOCAL(192.168.0.12)> alter table xxx set unused(M_ROW$$);

表已更改。

已用時間: 00: 00: 07.09
system@ORALOCAL(192.168.0.12)> alter table xxx drop unused columns;

表已更改。

已用時間: 00: 00: 02.74
system@ORALOCAL(192.168.0.12)> select table_name,COLUMN_NAME,decode(NULLABLE,'N','NOT NULL','Y',''),
2 DATA_TYPE,HIDDEN_COLUMN from user_tab_cols where table_name='XXX';

TABLE_NAME COLUMN_NAME DECODE(N DATA_TYPE HID
------------------------------ ------------------------------ -------- ---------- ---
XXX USERNAME NOT NULL VARCHAR2 NO
XXX USER_ID NOT NULL NUMBER NO
XXX PASSWORD VARCHAR2 NO
XXX ACCOUNT_STATUS NOT NULL VARCHAR2 NO
XXX LOCK_DATE DATE NO
XXX EXPIRY_DATE DATE NO
XXX DEFAULT_TABLESPACE NOT NULL VARCHAR2 NO
XXX TEMPORARY_TABLESPACE NOT NULL VARCHAR2 NO
XXX CREATED NOT NULL DATE NO
XXX PROFILE NOT NULL VARCHAR2 NO
XXX INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 NO
XXX EXTERNAL_NAME VARCHAR2 NO

已選擇12行。

已用時間: 00: 00: 01.05
system@ORALOCAL(192.168.0.12)>

已用時間: 00: 00: 00.55
system@ORALOCAL(192.168.0.12)>

如果深入一點，觀察一下這個包的操作過程，透過trace這個包的執行過，我們在start的時候，發現有以下的操作：

create snapshot log on "TEST"."XXX" with rowid;

create table "TEST"."MLOG$_XXX" (M_ROW$$ VARCHAR2(255), snaptime$$ date, dmltype$$ varchar2(1), old_new$$ varchar2(1), change_vector$$ raw(255)) pctfree 60 pctused 30;

alter table "TEST"."YYY" add (m_row$$ varchar2(255));

create snapshot "TEST"."YYY" on prebuilt table with reduced precision refresh fast with rowid as select * from "TEST"."XXX";

CREATE UNIQUE INDEX "TEST"."I_SNAP$_YYY" ON "TEST"."YYY" (M_ROW$$) ;

我們發現oracle是透過一個prebuilt 物化檢視來實現重定義的，因此，使用物化檢視的一些限制在這裡同樣適用。

其他注意點：（1）索引名稱改變。（2）如果資料量比較大，需要較多的undo。（3）如果遇到意外，需要abort_redef_table 將物化檢視取消掉。

http://www.oracleblog.org/study-note/online-redefine-table/

dbms_redefinition線上重定義表

相關文章