在线重定义分区表和NOLOGGING APPEND分区表对比

1、模拟大表
create table tes_p
(it int,
 ic int,
 im varchar2(20));

declare 
   i number(10);
begin
  for i in 1..1000000
  loop
  insert into tes_p
   values(i,i,'test');
  end loop;
end;
commit;
建立主键
alter table tes_p add constraint pk_t primary key (it);
方法1：在线重定义
建立中间表(分区表，你想得到的格式）
 CREATE TABLE scubagear
     (it int ,
      ic int ,
      im varchar2(20))
 PARTITION BY RANGE (ic)
     ( PARTITION s_q1 VALUES LESS THAN (333333),
       PARTITION s_q2 VALUES LESS THAN (666666),
       PARTITION s_q3 VALUES LESS THAN (10000002))

验证原表是否可以在线迁移
 exec dbms_redefinition.can_redef_table('ppzhu','tes_p');
新开启一个会话验证当前会话没有REDO产生
 select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
   where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
 
在新开会话进行迁移
SQL> exec dbms_redefinition.start_redef_table('ppzhu','tes_p','scubagear'); -8S
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_redefinition.sync_interim_table('ppzhu','tes_p','scubagear');
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_redefinition.finish_redef_table('ppzhu','tes_p','scubagear'); -1s
 
PL/SQL procedure successfully completed
查看数据已经移动完成，索引已经迁移
最后由于分区表并没有主键，我们建立主键
alter table tes_p add constraint pk_t_2 primary key (it);
此时你的分区表已经转换完成
查看这样操作的日志量
 select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
     where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       139 redo synch writes                                                        10
       139 redo synch time                                                         147
       139 redo entries                                                           9189
       139 redo size                                                          48358956  ---这里
       139 redo buffer allocation retries                                           30
       139 redo log space requests                                                   4
       139 redo log space wait time                                                 65
方法2
使用NOLOGGING方法
原表已经变化为scubagear，我们先建立分区表
 CREATE TABLE test_p2
     (it int ,
      ic int ,
      im varchar2(20))
 PARTITION BY RANGE (ic)
     ( PARTITION s_q1 VALUES LESS THAN (333333),
       PARTITION s_q2 VALUES LESS THAN (666666),
       PARTITION s_q3 VALUES LESS THAN (10000002))
让其不处于NOLOGGING模式
alter table test_p2 modify partition s_q1 nologging;
alter table test_p2 modify partition s_q2 nologging;
alter table test_p2 modify partition s_q3 nologging;
然后开启新会话进行INSERT APPEND
insert /* +append */ into test_p2 select * from scubagear; -9s
建立主键
alter table test_p2 add constraint pk_t_3 primary key (it);
这里完成了
然后RENAME
SQL> alter table  scubagear rename to test_123;
 
Table altered
 
SQL> alter table test_p2 rename to scubagear;
 
Table altered
表SCUBAGEAR已经是分区表
SQL>  select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
  2       where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       135 redo synch writes                                                         2
       135 redo synch time                                                          30
       135 redo entries                                                          22530
       135 redo size                                                          48463484 --这里
       135 redo buffer allocation retries                                            6
       135 redo log space requests                                                   4
       135 redo log space wait time                                                 17
 
7 rows selected

显示证明 两种方法REDO和时间差不多，时间也是相同的，不过为了可控尽量使用方法2.如果确实需要在线重定义使用方法1；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-767951/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-767951/