Oracle 利用在线重定义进行分区表转换

例如原始非分区表为

create table TESTUSER.LOG_TEST
(
  "id"         CHAR(36) primary key,
  "created_at" DATE
)

一、选择重定义方法

By key，选择主键或者所有字段有NOT NULL约束的唯一键用于在线重定义操作。使用这种方法，在线重定义之前和之后表应该有相同的主键字段（默认）
By rowid，如果没有主键可以使用这种方法，使用这种方法，隐藏字段M_ROW$$被添加到重定义后的表中。
我们表有主键(ID列)，选择By key方式

二、转换前准备

1. 验证表是否可在线重定义

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TESTUSER','LOG_TEST',DBMS_REDEFINITION.CONS_USE_PK);

如果表能作为在线重定义表的候选表，不会有结果返回；如果不能，这个过程会提示一个错误，说明为什么该表不能在线重定义。

2. 建空分区表作为中间表

不要加索引和约束，否则转换时会报错

create table TESTUSER.LOG_PAR
(
  "id"         CHAR(36),
  "created_at" DATE
)partition by range("created_at")
interval (numtodsinterval(1,'day')) --一天
(
   partition p_day_1 values less than (to_date('2019-09-20 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);

3. 启用会话并行功能（可选，加速）

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

三、进行转换

1. 开始在线重定义

会将源表复制一份，注意空间使用情况

exec DBMS_REDEFINITION.START_REDEF_TABLE('TESTUSER','LOG_TEST','LOG_PAR');

如果START_REDEF_TABLE因为某种原因失败，必须调用ABORT_REDEF_TABLE过程，否则接下来重新定义表将失败。

2. 同步依赖对象

若前面创建了索引或约束，再执行这步就会报错

variable err_num number;
exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'TESTUSER',orig_table=>'LOG_TEST',int_table=>'LOG_PAR',copy_indexes=>dbms_redefinition.cons_orig_params,num_errors=>:err_num);

3. 转换期间增量数据同步

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TESTUSER','LOG_TEST','LOG_PAR');

4. 完成在线重定义

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TESTUSER','LOG_TEST','LOG_PAR');

四、转换后工作

1. 表结构验证

-- 中间表变为非分区表
select table_name,partition_name from dba_tab_partitions where table_name='LOG_PAR';

-- 目标表变为分区表
select table_name,partition_name from dba_tab_partitions where table_name='LOG_TEST';

2. 删除中间表

drop table TESTUSER.LOG_PAR;

3. 创建存储过程，清理指定日期前数据

-- 删除p_table_name分区表p_before_date天前的数据
create or replace procedure snpcenter.drop_patition(p_before_date number,
                                                    p_table_name  varchar2) as
  v_sql            varchar(400);
  v_table_name     user_tab_partitions.table_name%type;
  v_partition_name user_tab_partitions.partition_name%type;
  v_high_value     varchar(200);
  v_created_date   date;
  v_req_date       date;
  cursor cur1 is
    select table_name, partition_name, high_value
      from user_tab_partitions
     where partition_name like 'SYS%' --第一个分区不能被drop
       and table_name = p_table_name;
begin
  open cur1;
  loop
    fetch cur1
      into v_table_name, v_partition_name, v_high_value;
    exit when cur1%notfound;
    v_created_date := to_date(substr(v_high_value, 12, 10), 'YYYY-MM-DD');
    v_req_date     := to_date(to_char(sysdate - p_before_date + 1,
                                      'YYYY-MM-DD'),
                              'YYYY-MM-DD');
    if v_created_date <= v_req_date then
        v_sql := 'alter table ' || v_table_name || ' drop partition ' ||
               v_partition_name;
      execute immediate v_sql;
     /* dbms_output.put_line(v_created_date);
      dbms_output.put_line(v_req_date);
      dbms_output.put_line(v_sql);
      dbms_output.put_line('---------------------------------------'); */
    end if;
  end loop;
  close cur1;
end;

调用方法

#直接调用
set serveroutput on
EXEC testuser.drop_patition(30,'LOG_TEST');

#job调用，每天凌晨2点删除30天前的分区（注意设置job的时候就会运行一次）
declare
v_job number;
begin
  dbms_job.submit(job       => v_job,
                  what      => 'testuser.drop_patition(30,''LOG'');',
                  interval  => 'TRUNC(sysdate) + 1 +2/24');
  commit;
end;

补充脚本，定期移动分区至不同表空间

create or replace package gtadmin.ilm_manage_pkg is

  /* p_from_days,p_to_days：移动创建时间在[p_from_days,p_to_days)时间范围内的（子）分区
  p_from_tbs：原（子）分区所在表空间
  p_to_tbs: 目标表空间
  p_owner: 表属主
  p_table_name: 待操作表*/

  procedure ilm_mv_partition(p_from_days  number,
                             p_to_days    number,
                             p_from_tbs   varchar2,
                             p_to_tbs     varchar2,
                             p_owner      varchar2,
                             p_table_name varchar2);

end ilm_manage_pkg;
/

create or replace package body gtadmin.ilm_manage_pkg is

  /*
     1. 移动p_before_date天前创建的表分区/子分区至指定表空间
     2. 表须以timestamp字段进行分区
     3. 执行需要有对应表及表空间权限
  */
  procedure ilm_mv_partition(p_from_days  number,
                             p_to_days    number,
                             p_from_tbs   varchar2,
                             p_to_tbs     varchar2,
                             p_owner      varchar2,
                             p_table_name varchar2) as
  
    v_table_name     all_tab_partitions.table_name%type;
    v_partition_name all_tab_partitions.partition_name%type;
    v_sub_par_count  all_tab_partitions.SUBPARTITION_COUNT%type;
    v_sub_par_name   all_tab_subpartitions.SUBPARTITION_NAME%type;
    v_high_value     varchar(200);
    v_sql            varchar(400);
    v_from_date      date;
    v_to_date        date;
    v_create_date    date;
  
    cursor cur_partitions is
      select a.table_name,
             a.partition_name,
             a.subpartition_count,
             a.high_value,
             b.subpartition_name
        from all_tab_partitions a, all_tab_subpartitions b
       where a.partition_name = b.partition_name
         and a.table_name = b.table_name
         and a.TABLE_OWNER = upper(p_owner)
         and a.table_name = upper(p_table_name)
         and (b.TABLESPACE_NAME = upper(p_from_tbs) or (b.TABLESPACE_NAME is null and a.TABLESPACE_NAME = upper(p_from_tbs)));
  
  begin
  
    v_from_date := to_date(to_char(sysdate - p_from_days + 1, 'YYYY-MM-DD'),
                           'YYYY-MM-DD');
  
    v_to_date := to_date(to_char(sysdate - p_to_days + 1, 'YYYY-MM-DD'),
                         'YYYY-MM-DD');
  
    open cur_partitions;
    loop
      fetch cur_partitions
        into v_table_name,
             v_partition_name,
             v_sub_par_count,
             v_high_value,
             v_sub_par_name;
      exit when cur_partitions%notfound;
    
      -- 将v_high_value转换为该分区创建时间
      SELECT TO_DATE(TO_CHAR(v_high_value / (1000 * 60 * 60 * 24) +
                             TO_DATE('1970-01-01 08:00:00',
                                     'YYYY-MM-DD HH24:MI:SS'),
                             'YYYY-MM-DD'),
                     'YYYY-MM-DD')
        into v_create_date
        FROM DUAL;
    
      -- 创建时间在[p_from_days,p_to_days)范围内
      if (v_create_date <= v_from_date) and (v_create_date > v_to_date) then
        -- 表无子分区，则仅移动分区
        if (v_sub_par_count = 0) then
          v_sql := 'alter table ' || p_owner || '.' || v_table_name ||
                   ' MOVE PARTITION ' || v_partition_name || ' TABLESPACE ' ||
                   p_to_tbs || ' online';
        
          /* dbms_output.put_line(v_create_date);
          dbms_output.put_line(v_from_date);
          dbms_output.put_line(v_to_date);
          dbms_output.put_line(v_sql);
          dbms_output.put_line('---------------------------------------'); */
          execute immediate v_sql;
        
          -- 表有子分区，则移动子分区
        else
  
          v_sql := 'alter table ' || p_owner || '.' || v_table_name ||
                   ' MOVE SUBPARTITION ' || v_sub_par_name ||
                   ' TABLESPACE ' || p_to_tbs || ' online';
  /*        dbms_output.put_line(v_create_date);
          dbms_output.put_line(v_from_date);
          dbms_output.put_line(v_to_date);
          dbms_output.put_line(v_sql);
          dbms_output.put_line('---------------------------------------');  */
          execute immediate v_sql;
        end if;
      end if;
    end loop;
    close cur_partitions;
  end;
end;
/

调用

variable job number;
begin
  sys.dbms_job.submit(job => :job,
                      what => 'gtadmin.ilm_manage_pkg.ilm_mv_partition(180,365,''GTADMIN'',''GTADMIN_WARM'',''GTADMIN'',''IM_HISMESSAGE'');',
                      next_date => to_date('2019-07-10 01:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                      interval => 'TRUNC(sysdate) + 1 +1/24');
end;
/

脚本2

同步旧数据

create or replace procedure gtadmin.data_sync(p_table_name varchar2,
                                      p_begin_date varchar2,
                                      p_end_date   varchar2) is

  v_begin_msgtime NUMBER(18);
  v_end_msgtime   NUMBER(18);
  v_sql           varchar(400);

begin
  -- 将输入日期转换为毫秒数
  SELECT TO_NUMBER(TO_DATE(p_begin_date, 'YYYY-MM-DD HH24:MI:SS') -
                   TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
    into v_begin_msgtime
    FROM DUAL;

  SELECT TO_NUMBER(TO_DATE(p_end_date, 'YYYY-MM-DD HH24:MI:SS') -
                   TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000+C6
    into v_end_msgtime
    FROM DUAL;

  v_sql := 'insert /*+ append parallel(gtadmin.' || p_table_name ||
           ',4) */ into gtadmin.' || p_table_name ||
           ' select /*+ parallel(i,4) */ * from gtadmin.' || p_table_name ||
           '@imdb_source i where >=' || v_begin_msgtime || ' and <' ||
           v_end_msgtime;

/*  dbms_output.put_line(v_begin_msgtime);
  dbms_output.put_line(v_end_msgtime);
  dbms_output.put_line(v_sql);
  dbms_output.put_line('---------------------------------------');*/
   dbms_output.put_line(v_sql);
   execute immediate v_sql;
   commit;

end data_sync;
/

调用

ALTER SESSION ENABLE PARALLEL DML;

exec data_sync(p_table_name => 'test0701',p_begin_date => '2019-06-01 00:00:00',p_end_date   => '2019-07-01 00:00:00');

秒客网

Oracle 利用在线重定义进行分区表转换

一、选择重定义方法

二、转换前准备

三、进行转换

四、转换后工作

相关文章

Oracle 利用在线重定义进行分区表转换

一、 选择重定义方法

二、 转换前准备

三、 进行转换

四、 转换后工作

相关文章

一、选择重定义方法

二、转换前准备

三、进行转换

四、转换后工作