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。
下面就来看看这个包的内容：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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来同步）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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已经比较多。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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等等的操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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%左右的浪费。
进行一次数据同步（其实是一次物化视图的刷新，后面会介绍）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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的变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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的一样，见下面的实验：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
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它：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
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的时候，发现有以下的操作：
|
1 2 3 4 5 6 7 8 9 |
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 将物化视图取消掉。