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 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and b.name like '%redo%' and a.VALUE0 ;
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 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and b.name like '%redo%' and a.VALUE0 ;
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 2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and b.name like '%redo%' and a.VALUE0 ;
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；