在线重定义功能

文章来自：

http://www.lunar2013.com/2014/07/%E6%B5%8B%E8%AF%95%E5%9C%A8%E7%BA%BF%E9%87%8D%E5%AE%9A%E4%B9%89%E5%8A%9F%E8%83%BD.html

9i开始，Oracle引入了在线重定义功能，但是bug比较多，10g时，如果数据量比较大，有些特殊场景，也有bug。
因此，前几天有同事需要测试在线重定义的功能，我查了下MOS，做个demo，做一个功能测试，如果生产上在低版本数据库执行在线重定义功能时，请仔细查看MOS上相关的常见问题。

22:28:49 SQL> select * from v $version;   BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL /SQL Release 10.2.0.4.0 - Production CORE    10.2.0.4.0      Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production   已用时间:  00: 00: 00.01 22:30:24 SQL> select 'www.lunar2013.com' lunar,sysdate from dual;   LUNAR                            SYSDATE -------------------------------- -------------- www.lunar2013.com                05-7月 -14   已用时间:  00: 00: 00.00 22:31:11 SQL>

–创建测试表

CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date );   alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));   -- load table with 1,000,000 rows begin      for i in 1 .. 1000      loop          for j in 1 .. 1000          loop          insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );          end loop;      end loop; end; / commit;

–收集统计信息

EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table' , cascade => TRUE); SELECT num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE' ;

–创建空的分区表

CREATE TABLE par_table ( a NUMBER, y number, name VARCHAR2(100),date_used DATE) PARTITION BY RANGE (date_used) (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE( '10/07/2012' , 'DD/MM/YYYY' )), PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE( '15/07/2012' , 'DD/MM/YYYY' )), PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

–执行Redefinition.can_redef_table，验证unpar_table表是否可以在线重定义,如果不可以会给出建议：
EXEC Dbms_Redefinition.can_redef_table(USER, ‘unpar_table’);
执行这一步的时候，如果缺少如下权限，那么会报如下错误：
第 1 行出现错误:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须声明标识符 ‘DBMS_REDEFINITION’
ORA-06550: 第 1 行, 第 7 列:
解决方法：
grant execute on dbms_redefinition to lunar;

-- This procedure (DBMS_REDEFINITION.start_redef_table) creates a materialized view based on a CTAS, as we can see below with -- the PREBUILT container table. BEGIN DBMS_REDEFINITION.start_redef_table( uname => USER, orig_table => 'unpar_table' , int_table => 'par_table' ); END; /

执行这一步的时候，如果缺少如下权限，那么会报如下错误：
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 “SYS.DBMS_REDEFINITION”, line 50
ORA-06512: 在 “SYS.DBMS_REDEFINITION”, line 1343
ORA-06512: 在 line 2
解决方法：
grant create any table to lunar;
grant alter any table to lunar;
grant drop any table to lunar;
grant lock any table to lunar;
grant select any table to lunar;

22:16:46 SQL> select mview_name,container_name, build_mode from user_mviews;   MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD ------------------------------ ------------------------------ --------- PAR_TABLE                      PAR_TABLE                      PREBUILT

–开启DBMS_REDEFINITION.start_redef_table后，向unpar_table表中插入1000行数据
此时，系统会使用mview log来记录该表的变化，可以查询MLOG$_UNPAR_TABLE来确认这一点：

begin      for i in 1001 .. 1010      loop          for j in 1001 .. 1100          loop          insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );          end loop;      end loop; end; / commit;   22:17:07 SQL> select count(*) from MLOG$_UNPAR_TABLE;      COUNT(*) ----------        1000   已用时间:  00: 00: 00.01 22:17:12 SQL> select count(*) from unpar_table;      COUNT(*) ----------     1001000   已用时间:  00: 00: 00.07 22:17:27 SQL> select count(*) from par_table;      COUNT(*) ----------     1000000   已用时间:  00: 00: 00.20 22:17:48 SQL>

–执行dbms_redefinition.sync_interim_table，类似MVIEW FAST REFRESH
–该操作将MLOG$_UNPAR_TABLE的内容同步到par_table，并在同步后purge自己
–在执行dbms_redefinition.finish_redef_table之前，可以执行多次

BEGIN dbms_redefinition.sync_interim_table( uname => USER, orig_table => 'unpar_table' , int_table => 'par_table' ); END;   22:18:35 SQL> select count(*) from MLOG$_UNPAR_TABLE;      COUNT(*) ----------           0   已用时间:  00: 00: 00.00 22:22:52 SQL> select count(*) from unpar_table;      COUNT(*) ----------     1001000   已用时间:  00: 00: 00.06 22:22:58 SQL> select count(*) from par_table;      COUNT(*) ----------     1001000   已用时间:  00: 00: 00.06 22:23:03 SQL>   ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y)); EXEC DBMS_STATS.gather_table_stats(USER, 'par_table' , cascade => TRUE);

–完成在线重定义的操作，切换两个表：

BEGIN dbms_redefinition.finish_redef_table( uname => USER, orig_table => 'unpar_table' , int_table => 'par_table' ); END; /   22:27:49 SQL> select count(*) from MLOG$_UNPAR_TABLE; select count(*) from MLOG$_UNPAR_TABLE                       * 第 1 行出现错误: ORA-00942: 表或视图不存在     已用时间:  00: 00: 00.00 22:28:02 SQL> select count(*) from unpar_table;      COUNT(*) ----------     1001000   已用时间:  00: 00: 00.06 22:28:10 SQL> select count(*) from par_table;      COUNT(*) ----------     1001000   已用时间:  00: 00: 00.06 22:28:15 SQL>   22:28:32 SQL> SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE' ;   PAR --- YES   已用时间:  00: 00: 00.02 22:28:34 SQL> SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE' ;   PARTITION_NAME                   NUM_ROWS ------------------------------ ---------- UNPAR_TABLE_12                     274384 UNPAR_TABLE_15                       5000 UNPAR_TABLE_MX                     721140   已用时间:  00: 00: 00.13 22:28:38 SQL>   22:28:38 SQL> drop TABLE par_table cascade constraints;   表已删除。   已用时间:  00: 00: 00.06 22:28:49 SQL>