【Oracle SQL】在线重定义表结构

---

1. 创建临时表

--源表：
--收集统计信息
BEGIN
  dbms_stats.gather_table_stats(ownname => 'USER1', tabname => 'TABLETEST1', cascade => true);
END;
/

--创建临时分区表（表结构和要转换的堆表保持一致）
--时间分区
CREATE TABLE par_test1(id NUMBER, createdate DATE) 
PARTITION BY RANGE(createdate) INTERVAL (NUMTOYMINTERVAL (1, 'MONTH')) --自动扩展分区
(
partition p1 values less than(to_date('2019-8-1', 'yyyy-mm-dd'))
);
alter table par_test1 add constraint par_test1_pk primary key (ID) using INDEX;

--hash分区
create table par_test1 (   id         NUMBER,
  createdate DATE) 
partition by hash (id)
(
  partition NN_PARA_SITE_PARA01
    tablespace USERS,
  partition NN_PARA_SITE_PARA02
    tablespace USERS
);

2. 检查重定义的合理性

begin
  DBMS_REDEFINITION.CAN_REDEF_TABLE(uname        =>'USER1',
                                    tname        =>'TABLETEST1',
                                    options_flag => dbms_redefinition.cons_use_pk);
end;

begin
  DBMS_REDEFINITION.CAN_REDEF_TABLE(uname        =>'USER1',
                                    tname        =>'TABLETEST1',
                                    options_flag => dbms_redefinition.cons_use_rowid);
end;

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

3. 在线重定义

--在线重定义
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'USER1',
                                      orig_table  => 'TABLETEST1',
                                      int_table   => 'TABLETEST2',
                                     options_flag => dbms_redefinition.cons_use_pk
                                      );
END;

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'USER1',
                                      orig_table  => 'TABLETEST1',
                                      int_table   => 'TABLETEST2',
                                     options_flag => dbms_redefinition.cons_use_rowid
                                      );
END;

--同步数据
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => 'USER1',
                                       orig_table => 'TABLETEST1',
                                       int_table  => 'TABLETEST2'
                                       );
END;

--结束重定义
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => 'USER1',
                                       orig_table => 'TABLETEST1',
                                       int_table  => 'TABLETEST2'
                                       );
END;

--中断重定义，可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行
BEGIN

  DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname      => 'USER1',
                                      orig_table => 'TABLETEST1',
                                      int_table  => 'TABLETEST2'
                                      );
 END;

--删除临时表
BEGIN

  truncate table P1ODSDA1.TABLETEST2;
  drop table P1ODSDA1.TABLETEST2 purge;

END;

--查看分区结果
select * from user_tab_partitions a where a.table_name='TABLETEST1';

4. 在线重定义脚本

--primary key
DECLARE
  v_uname VARCHAR2(30);
  v_otab  VARCHAR2(30);
  v_ntab  VARCHAR2(30);
BEGIN
  v_uname := 'USER1';
  v_otab  := 'TABLETEST1';
  v_ntab  := 'TABLETEST2';
  DBMS_REDEFINITION.CAN_REDEF_TABLE(uname        => v_uname,
                                    tname        => v_otab,
                                    options_flag => dbms_redefinition.cons_use_pk);
                                    
  DBMS_REDEFINITION.START_REDEF_TABLE(uname        => v_uname,
                                      orig_table   => v_otab,
                                      int_table    => v_ntab,
                                      options_flag => dbms_redefinition.cons_use_pk);

  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => v_uname,
                                       orig_table => v_otab,
                                       int_table  => v_ntab);
                                       
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => v_uname,
                                       orig_table => v_otab,
                                       int_table  => v_ntab);
END;
          
--rowid
DECLARE
  v_uname VARCHAR2(30);
  v_otab  VARCHAR2(30);
  v_ntab  VARCHAR2(30);
BEGIN
  v_uname := 'USER1';
  v_otab  := 'TABLETEST1';
  v_ntab  := 'TABLETEST2';
  DBMS_REDEFINITION.CAN_REDEF_TABLE(uname        => v_uname,
                                    tname        => v_otab,
                                    options_flag => dbms_redefinition.cons_use_rowid);
                                    
  DBMS_REDEFINITION.START_REDEF_TABLE(uname        => v_uname,
                                      orig_table   => v_otab,
                                      int_table    => v_ntab,
                                      options_flag => dbms_redefinition.cons_use_rowid);

  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname      => v_uname,
                                       orig_table => v_otab,
                                       int_table  => v_ntab);
                                       
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname      => v_uname,
                                       orig_table => v_otab,
                                       int_table  => v_ntab);
END;