测试在线重定义功能

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>