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
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

---------- ------------------------------------------------------ ----------

153 redo synch writes 8

153 redo synch time 76

153 redo entries 4646

153 redo size 23206200 ---这里完全是CREATE TABLE LOGGING的日志量，可以判断这里是做了一个CATS

153 redo buffer allocation retries 22

153 redo log space requests 2

153 redo log space wait time 10

查看数据已经移动完成，索引已经迁移
最后由于分区表并没有主键，我们建立主键
alter table tes_p add constraint pk_t_2 primary key (it);
此时你的分区表已经转换完成
查看这样操作的日志量
SQL> 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
---------- -------------------------------------------------------- ----------
153   redo synch writes 10
153   redo synch time    115
153   redo entries    8214
153   redo size    45263352 ---加上了create 主键的日志量
153   redo buffer allocation retries    37
153   redo log space requests 2
153   redo log space wait time    10
方法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

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
---------- ---------------------------------------------- ----------
143 redo synch writes    1
143 redo synch time    34
143 redo entries 18812 --基本日志非常少
143 redo size 26530368
143 redo buffer allocation retries 26

建立主键
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
  where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;

SID NAME VALUE

---------- --------------------------------- ----------

143 redo synch writes 4

143 redo synch time 22

143 redo entries 22593

143 redo size 48548136 --为什么这里多这么多近45m的日志，我估计直接路径加载进行了延迟块清除。但是我没有DUMP 数据块来看。

143 redo buffer allocation retries 25

143 redo log space requests 2

143 redo log space wait time 7

7 rows selected
但是如果再次执行
alter table test_p2 drop constraint pk_t_3;
alter table test_p2 add constraint pk_t_3 primary key (it);

SQL>
SQL>   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
---------- -------------------------------------------------------- ----------
143   redo synch writes    8
143   redo synch time    32
143   redo entries    26116
143   redo size    70526716 --基本上正常了。
143   redo buffer allocation retries 31
143   redo log space requests 2
143   redo log space wait time 7

7 rows selected
方法3
直接使用CREATE TABLE NOLOGGING方式
      CREATE TABLE asd
      PARTITION BY RANGE (ic)
           ( PARTITION s_q1 VALUES LESS THAN (333333) nologging,
             PARTITION s_q2 VALUES LESS THAN (666666) nologging,
             PARTITION s_q3 VALUES LESS THAN (10000002) nologging)
             as
      select * from SCUBAGEAR;
--1.4S

Table created

SQL>
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
---------- ---------------------------------------------------------------- ----------
137 redo synch writes                                                         1
137 redo synch time                                                          19
137    redo entries                                                           1744 --这里也非常少
137 redo size                                                            279604

SQL> alter table asd add constraint pk_t_4 primary key (it);

Table altered

SQL>
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
---------- ----------------------------------------------- ----------
137 redo synch writes    4
137 redo synch time    42
137 redo entries     5311
137 redo size 22324744 ---这里就是表的大小基本上，也没有异常
137 redo buffer allocation retries    14

接下来就RENAME就好了

实验证明：
1、应该来说在线重定义如果在表修改很少的情况下，基本上生成的日志就是CATS LOGGING，
2、如果想流程更加可控可以使用方法2，方法3.经过实验方法3最快，但是实际上日志其实差不多，但是DDL是不写UNDO得，所以其速度非常快仅仅为1.4S远远快于方法1,方法2

3、如果需要在想重定义，没办法只有方法1.

ITPub博客

普通表转变为分区表

最新文章