dbms_redefinition在线重定义表

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/online-redefine-table/

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 将物化视图取消掉。

· 【文章发布信息】发表于： 2007-11-11 @ 02:51:22 · ||分类： Study note

留条评论