普通表改成分区表的方法

有一系列普通表都有几十到几百GB这么大，数据从几亿到几十亿，现在想将这些表改造成分区表，用其中的时间或者其他字段来做分区，允许有一段停机时间来停这些表相关的应用，该如何做呢？

思路：
新建一张分区表，按日期建分区，确保分区表各字段和属性都和普通表一样。然后停应用，将普通表记录插入到分区表中。然后将普通表重命名，分区表命名成原表的名字，完成任务。

 

将原表重命名为_yyyymmdd格式的表名：

create or replace procedure p_rename_001(p_tab in varchar2) as
  /*
  功能：将原表重命名为_yyyymmdd格式的表名
  完善点：要考虑RENMAE的目标表已存在的情况，先做判断
  */
  v_cnt_re_tab   number(9) := 0;
  v_sql_p_rename varchar2(4000);
  yyyymmdd       varchar2(8);
begin
  select to_char(sysdate, 'yyyymmdd') into yyyymmdd from dual;

  select count(*)
    into v_cnt_re_tab
    from user_objects
   where object_name = upper(p_tab || '_' || yyyymmdd);
  if v_cnt_re_tab = 0 then
    v_sql_p_rename := 'rename ' || p_tab || ' to ' || p_tab || '_' ||
                      yyyymmdd;
    -- DBMS_OUTPUT.PUT_LINE(v_sql_p_rename);--调试使用
    p_insert_log(p_tab,
                 'P_RENAME',
                 v_sql_p_rename,
                 '完成原表的重命名，改为_YYYYMMDD形式',
                 1);
    execute immediate (v_sql_p_rename); --这里无须做判断，rename动作真实完成！如果后续只是为生成脚本而不是真实执行分区操作，最后再把这个表RENAME回去！
  else
    raise_application_error(-20066,
                            '备份表' || p_tab || '_' || yyyymmdd ||
                            '已存在,请先删除或重命名该备份表后再继续执行！');
    -- DBMS_OUTPUT.PUT_LINE('备份表'||P_TAB||'_'||YYYYMMDD||'已存在');
  end if;
  dbms_output.put_line('操作步骤1(备份原表)-------将' || p_tab || ' 表RENMAE成 ' ||
                       p_tab || '_' || yyyymmdd || '，并删除其约束索引等');
end p_rename_001;

用CREATE TABLE AS SELECT 的方式从RENAME的_yyyymmdd表中新建出一个只有MAXVALUE的初步分区表：

create or replace procedure p_ctas_002
(
  p_tab         in varchar2,
  p_struct_only in number,
  p_deal_flag   in number,
  p_part_colum  in varchar2,
  p_parallel    in number default 4,
  p_tablespace  in varchar2
) as
  /*
  功能：用CREATE TABLE AS SELECT 的方式从RENAME的_yyyymmdd表中新建出一个只有MAXVALUE的初步分区表
  完善点：要考虑并行，nologging 的提速方式，也要考虑最终将NOLOGGING和PARALLEL恢复成正常状态
  */
  v_sql_p_ctas varchar2(4000);
begin
  v_sql_p_ctas := 'create table ' || p_tab || ' partition by range ( ' ||
                  p_part_colum || ' ) (' ||
                  ' partition P_MAX  values less than (maxvalue))' ||
                  ' nologging parallel 4  tablespace ' || p_tablespace ||
                  ' as select /*+parallel(t,' || p_parallel || ')*/ *' ||
                  ' from ' || p_tab || '_' || yyyymmdd;

  if p_struct_only = 0 then
    v_sql_p_ctas := v_sql_p_ctas || ' where 1=2';
  else
    v_sql_p_ctas := v_sql_p_ctas || ' where 1=1';
  end if;
  --DBMS_OUTPUT.PUT_LINE(v_sql_p_ctas);--调试使用
  p_insert_log(p_tab, 'p_ctas', v_sql_p_ctas, '完成CTAS建初步分区表', 2, 1);
  p_if_judge(v_sql_p_ctas, p_deal_flag);
  v_sql_p_ctas := 'alter table ' || p_tab || ' logging';
  p_insert_log(p_tab,
               'p_ctas',
               v_sql_p_ctas,
               '将新分区表修改回LOGGING属性',
               2,
               2);
  p_if_judge(v_sql_p_ctas, p_deal_flag);
  v_sql_p_ctas := 'alter table ' || p_tab || ' noparallel';
  p_insert_log(p_tab,
               'p_ctas',
               v_sql_p_ctas,
               '将新分区表修改回NOPARALLEL属性',
               2,
               3);
  p_if_judge(v_sql_p_ctas, p_deal_flag);
  dbms_output.put_line('操作步骤2(建分区表)-------通过CTAS的方式从 ' || p_tab || '_' ||
                       yyyymmdd || ' 中新建' || p_tab || '表，完成初步分区改造工作');
end p_ctas_002;

对分区表进行分区SPLIT工作

create or replace procedure p_split_part_003
(
  p_tab            in varchar2,
  p_deal_flag      in number,
  p_part_nums      in number default 24,
  p_tab_tablespace in varchar2
) as
  /*
  功能：用CREATE TABLE AS SELECT 的方式新建出一个只有MAXVALUE的初步分区表进行SPLIT，
  按月份进行切分，默认p_part_nums产生24个分区，构造2年的分区表，第一个分区为当前月的
  上一个月
  */
  v_first_day        date;
  v_next_day         date;
  v_prev_day         date;
  v_sql_p_split_part varchar2(4000);
begin
  select to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd')
    into v_first_day
    from dual;
  for i in 1 .. p_part_nums loop
    select add_months(v_first_day, i) into v_next_day from dual;
    select add_months(v_next_day, -1) into v_prev_day from dual;
    v_sql_p_split_part := 'alter table ' || p_tab ||
                          ' split partition p_MAX at ' || '(to_date(''' ||
                          to_char(v_next_day, 'yyyymmdd') ||
                          ''',''yyyymmdd''))' || 'into (partition PART_' ||
                          to_char(v_prev_day, 'yyyymm') || ' tablespace ' ||
                          p_tab_tablespace || ', partition p_MAX)';
    -- DBMS_OUTPUT.PUT_LINE(v_sql_p_split_part);--调试使用
    p_insert_log(p_tab,
                 'p_split_part',
                 v_sql_p_split_part,
                 '分区表完成分区SPLIT工作',
                 3,
                 i);
    p_if_judge(v_sql_p_split_part, p_deal_flag);
  end loop;
  dbms_output.put_line('操作步骤3(分区操作)-------对新建的' || p_tab ||
                       '分区表完成分区SPLIT工作');
end p_split_part_003;

从_YYYYMMDD备份表中得到表的注释，为新分区表的表名增加注释

create or replace procedure p_tab_comments_004
(
  p_tab       in varchar2,
  p_deal_flag in number
) as
  /*
  功能：从_YYYYMMDD备份表中得到表的注释，为新分区表的表名增加注释
  */
  v_sql_p_tab_comments varchar2(4000);
  v_cnt                number;
begin
  select count(*)
    into v_cnt
    from user_tab_comments
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and comments is not null;
  if v_cnt > 0 then
    for i in (select *
                from user_tab_comments
               where table_name = upper(p_tab) || '_' || yyyymmdd
                 and comments is not null) loop
      v_sql_p_tab_comments := 'comment on table ' || p_tab || ' is ' || '''' ||
                              i.comments || '''';
      -- DBMS_OUTPUT.PUT_LINE(v_sql_p_deal_tab_comments);--调试使用
      p_insert_log(p_tab,
                   'p_deal_comments',
                   v_sql_p_tab_comments,
                   '将新分区表的表的注释加上',
                   4,
                   1);
      p_if_judge(v_sql_p_tab_comments, p_deal_flag);
    end loop;
    dbms_output.put_line('操作步骤4(表的注释)-------对' || p_tab || '表增加表名的注释内容');
  else
    dbms_output.put_line('操作步骤4(表的注释)-------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有表注释!');
  end if;
end p_tab_comments_004;

从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释

create or replace procedure p_col_comments_005
(
  p_tab       in varchar2,
  p_deal_flag in number
) as
  /*
  功能：从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释
  */
  v_sql_p_col_comments varchar2(4000);
  v_cnt                number;
begin
  select count(*)
    into v_cnt
    from user_col_comments
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and comments is not null;
  if v_cnt > 0 then
    for i in (select *
                from user_col_comments
               where table_name = upper(p_tab) || '_' || yyyymmdd
                 and comments is not null) loop
      v_sql_p_col_comments := 'comment on column ' || p_tab || '.' ||
                              i.column_name || ' is ' || '''' || i.comments || '''';
      p_insert_log(p_tab,
                   'p_deal_col_comments',
                   v_sql_p_col_comments,
                   '将新分区表的列的注释加上',
                   5,
                   1);
      p_if_judge(v_sql_p_col_comments, p_deal_flag);
    end loop;
    dbms_output.put_line('操作步骤5(列的注释)-------对' || p_tab || '表增加列名及字段的注释内容');
  else
    dbms_output.put_line('操作步骤5(列的注释)-------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有列注释!');
  end if;
end p_col_comments_005;

从_YYYYMMDD备份表中得到原表的DEFAULT值，为新分区表的表名和字段增加DEFAULT值

create or replace procedure p_defau_and_null_006
(
  p_tab       in varchar2,
  p_deal_flag in number
) as
  /*
  功能：从_YYYYMMDD备份表中得到原表的DEFAULT值，为新分区表的表名和字段增加DEFAULT值
  */
  v_sql_defau_and_null varchar2(4000);
  v_cnt                number;
begin
  select count(*)
    into v_cnt
    from user_tab_columns
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and data_default is not null;
  if v_cnt > 0 then
    for i in (select *
                from user_tab_columns
               where table_name = upper(p_tab) || '_' || yyyymmdd
                 and data_default is not null) loop
      v_sql_defau_and_null := 'alter table ' || p_tab || ' modify ' ||
                              i.column_name || ' default ' ||
                              i.data_default;
      p_insert_log(p_tab,
                   'p_deal_default',
                   v_sql_defau_and_null,
                   '将新分区表的默认值加上',
                   6);
      p_if_judge(v_sql_defau_and_null, p_deal_flag);
    end loop;
    dbms_output.put_line('操作步骤6(空和默认)-------对' || p_tab ||
                         '表完成默认DEFAULT值的增加');
  else
    dbms_output.put_line('操作步骤6(空和默认)-------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有DEFAULT或NULL值!');
  end if;
end p_defau_and_null_006;

从_YYYYMMDD备份表中得到原表的CHECK值，为新分区表增加CHECK值

create or replace procedure p_check_007
(
  p_tab       in varchar2,
  p_deal_flag in number
) as
  /*
  功能：从_YYYYMMDD备份表中得到原表的CHECK值，为新分区表增加CHECK值
  另注：
  user_constraints已经进行了非空的判断，可以略去如下类似的从user_tab_columns获取非空判断的代码编写来判断是否
  for i in (select * from user_tab_columns where table_name=UPPER(P_TAB)||'_' ||YYYYMMDD and nullable='N') loop
  v_sql:='alter table '||p_tab||' modify '||i.COLUMN_NAME ||' not null';
  */
  v_sql_p_check varchar2(4000);
  v_cnt         number;
begin
  select count(*)
    into v_cnt
    from user_constraints
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and constraint_type = 'C';
  if v_cnt > 0 then
    for i in (select *
                from user_constraints
               where table_name = upper(p_tab) || '_' || yyyymmdd
                 and constraint_type = 'C') loop
      v_sql_p_check := 'alter table ' || p_tab || '_' || yyyymmdd ||
                       ' drop constraint ' || i.constraint_name;
      p_insert_log(p_tab,
                   'p_deal_check',
                   v_sql_p_check,
                   '将备份出来的原表的CHECK删除',
                   7,
                   1);
      p_if_judge(v_sql_p_check, p_deal_flag);
      v_sql_p_check := 'alter table ' || p_tab || ' ADD CONSTRAINT ' ||
                       i.constraint_name || ' CHECK (' ||
                       i.search_condition || ')';
      p_insert_log(p_tab,
                   'p_deal_check',
                   v_sql_p_check,
                   '将新分区表的CHECK加上',
                   7,
                   2);
      p_if_judge(v_sql_p_check, p_deal_flag);
    end loop;
    dbms_output.put_line('操作步骤7(check约束)-------对' || p_tab || '完成CHECK的约束');
  else
    dbms_output.put_line('操作步骤7(check约束)-----' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有CHECK!');
  end if;
end p_check_007;

从_YYYYMMDD备份表中得到原表的索引信息，为新分区表增加普通索引（唯一和非唯一索引，函数索引暂不考虑），并删除旧表索引

create or replace procedure p_index_008
(
  p_tab            in varchar2,
  p_deal_flag      in number,
  p_idx_tablespace in varchar2
) as
  /*
  功能：从_YYYYMMDD备份表中得到原表的索引信息，为新分区表增加普通索引（唯一和非唯一索引，函数索引暂不考虑），并删除旧表索引
  难点：需要考虑联合索引的情况
  */
  v_sql_p_normal_idx varchar2(4000);
  v_cnt              number;
begin
  select count(*)
    into v_cnt
    from user_indexes
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and index_type = 'NORMAL'
     and index_name not in (select constraint_name from user_constraints);
  if v_cnt > 0 then
    for i in (with t as
                 (select c.*, i.uniqueness
                   from user_ind_columns c,
                        (select distinct index_name, uniqueness
                           from user_indexes
                          where table_name = upper(p_tab) || '_' || yyyymmdd
                            and index_type = 'NORMAL'
                            and index_name not in
                                (select constraint_name from user_constraints)) i
                  where c.index_name = i.index_name)
                select index_name,
                       table_name,
                       uniqueness,
                       max(substr(sys_connect_by_path(column_ name, ','), 2)) str ---考虑组合索引的情况
                  from (select column_name,
                               index_name,
                               table_name,
                               row_number() over(partition by index_name, table_name order by column_name) rn,
                               uniqueness
                          from t) t
                 start with rn = 1
                connect by rn = prior rn + 1
                       and index_name = prior index_name
                 group by index_name, t.table_name, uniqueness) loop
      v_sql_p_normal_idx := 'drop index ' || i.index_name;
      p_insert_log(p_tab,
                   'p_deal_normal_idx',
                   v_sql_p_normal_idx,
                   '删除原表索引',
                   8,
                   1);
      p_if_judge(v_sql_p_normal_idx, p_deal_flag);
      dbms_output.put_line('操作步骤8(处理索引)-------将' || i.table_name || '的' ||
                           i.str || '列的索引' || i.index_name || '删除完毕');
      if i.uniqueness = 'UNIQUE' then
        v_sql_p_normal_idx := 'CREATE UNIQUE INDEX ' || i.index_name ||
                              ' ON ' || p_tab || '(' || i.str || ')' ||
                              ' tablespace ' || p_idx_tablespace;
      elsif i.uniqueness = 'NONUNIQUE' then
        v_sql_p_normal_idx := 'CREATE  INDEX ' || i.index_name || ' ON ' ||
                              p_tab || ' (' || i.str || ')' ||
                              ' LOCAL tablespace ' || p_idx_tablespace;
      end if;
      p_insert_log(p_tab,
                   'p_deal_normal_idx',
                   v_sql_p_normal_idx,
                   '将新分区表的索引加上',
                   8,
                   2);
      p_if_judge(v_sql_p_normal_idx, p_deal_flag);
      dbms_output.put_line('操作步骤8(处理索引)-------对' || p_tab || '新分区表' ||
                           i.str || '列增加索引' || i.index_name);
    end loop;
  else
    dbms_output.put_line('操作步骤8(处理索引)-------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有索引(索引模块并不含主键判断)!');
  end if;
end p_index_008;

从_YYYYMMDD备份表中得到原表的主键信息，为新分区表增加主键值，并删除旧表主键

create or replace procedure p_pk_009
(
  p_tab            in varchar2,
  p_deal_flag      in number,
  p_idx_tablespace in varchar2
) as
  /*
  功能：从_YYYYMMDD备份表中得到原表的主键信息，为新分区表增加主键值，并删除旧表主键
  难点：需要考虑联合主键的情况
  */
  v_sql_p_pk varchar2(4000);
  v_cnt      number;

begin
  select count(*)
    into v_cnt
    from user_ind_columns
   where index_name in (select index_name
                          from sys.user_constraints t
                         where table_name = upper(p_tab) || '_' || yyyymmdd
                           and constraint_type = 'P');
  if v_cnt > 0 then
    for i in (with t as
                 (select index_name, table_name, column_name
                   from user_ind_columns
                  where index_name in
                        (select index_name
                           from sys.user_constraints t
                          where table_name = upper(p_tab) || '_' || yyyymmdd
                            and constraint_type = 'P'))
                select index_name,
                       table_name,
                       max(substr(sys_connect_by_path(column_name, ','), 2)) str
                  from (select column_name,
                               index_name,
                               table_name,
                               row_number() over(partition by index_name, table_name order by column_name) rn
                          from t) t
                 start with rn = 1
                connect by rn = prior rn + 1
                       and index_name = prior index_name
                 group by index_name, t.table_name) loop
      v_sql_p_pk := 'alter table ' || i.table_name || ' drop constraint ' ||
                    i.index_name || ' cascade';
      p_insert_log(p_tab,
                   'p_deal_pk',
                   v_sql_p_pk,
                   '将备份出来的原表的主键删除',
                   9,
                   1);
      p_if_judge(v_sql_p_pk, p_deal_flag);
      dbms_output.put_line('操作步骤9(处理主键)-------将备份出来的原表' || i.table_name || '的' ||
                           i.str || '列的主键' || i.index_name || '删除完
毕！'); ---放在FOR循环中效率没问题，因为主键只有一个，只会循环一次
      v_sql_p_pk := 'ALTER TABLE ' || p_tab || ' ADD CONSTRAINT ' ||
                    i.index_name || ' PRIMARY KEY (' || i.str || ')' || ' using index tablespace
' || p_idx_tablespace;
      p_insert_log(p_tab,
                   'p_deal_pk',
                   v_sql_p_pk,
                   '将新分区表的主键加上',
                   9,
                   2);
      p_if_judge(v_sql_p_pk, p_deal_flag);
      dbms_output.put_line('操作步骤9(处理主键)-------对' || p_tab || '表的' || i.str ||
                           '列增加主键' || i.index_name); ---放在FOR循环中效率没问题，因为主键只有一个，只会循环一次
    end loop;
  else
    dbms_output.put_line('操作步骤9(处理主键)-------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有主键!');
  end if;
end p_pk_009;

从_YYYYMMDD备份表中得到原表的外键或主键约束，为新分区表增加外键或主键，并删除旧表的外键或主键

create or replace procedure p_constraint_010
(
  p_tab       in varchar2,
  p_deal_flag in number
) as
  /*
  功能：从_YYYYMMDD备份表中得到原表的约束，为新分区表增加约束值，并删除旧表约束
  难点：需要考虑联合外键REFERENCE的情况
  */
  v_sql_p_constraint varchar2(4000);
  v_cnt              number;
begin
  select count(*)
    into v_cnt
    from user_constraints
   where table_name = upper(p_tab) || '_' || yyyymmdd
     and constraint_type = 'R';
  if v_cnt > 0 then
    for i in (with t1 as
                 (select /*+no_merge */
                  position,
                  t.owner,
                  t.constraint_name as constraint_name1,
                  t.table_name      as table_name1,
                  t.column_name     as column_name1
                   from user_cons_columns t
                  where constraint_name in
                        (select constraint_name
                           from user_constraints
                          where table_name = upper(p_tab) || '_' || yyyymmdd
                            and constraint_type = 'R')),
                t2 as
                 (select /*+no_merge */
                  t.position,
                  c.constraint_name constraint_name1,
                  t.constraint_name as constraint_name2,
                  t.table_name as table_name2,
                  t.column_name as column_name2,
                  max(t.position) over(partition by c.constraint_name) max_position
                   from user_cons_columns t, user_constraints c
                  where c.table_name = upper(p_tab) || '_' || yyyymmdd
                    and t.constraint_name = c.r_constraint_name
                    and c.constraint_type = 'R'),
                t3 as
                 (select t1.*,
                        t2.constraint_name2,
                        t2.table_name2,
                        t2.column_name2,
                        t2.max_position
                   from t1, t2
                  where t1.constraint_name1 = t2.constraint_name1
                    and t1.position = t2.position)
                select t3.*,
                       substr(sys_connect_by_path(column_name1, ','), 2) as fk,
                       substr(sys_ connect_by_path(column_name2, ','), 2) as pk
                  from t3
                 where position = max_position
                 start with position = 1
                connect by constraint_name1 = prior constraint_name1
                       and position = prior position + 1) loop
      v_sql_p_constraint := 'alter table ' || p_tab || '_' || yyyymmdd ||
                            ' drop constraint ' || i.constraint_name1;
      p_insert_log(p_tab,
                   'p_deal_constraint',
                   v_sql_p_constraint,
                   '删除原表FK外键',
                   10,
                   1);
      p_if_judge(v_sql_p_constraint, p_deal_flag);
      dbms_output.put_line('操作步骤10(处理外键)------将备份出来的' || i.table_name1 || '表的' ||
                           i.column_name1 || '列的外键' || i.constraint_name1 ||
                           '删除完毕!');
      v_sql_p_constraint := 'alter table ' || p_tab || ' add constraint ' ||
                            i.constraint_
                            name1 || ' foreign key ( ' || i.fk ||
                            ') references ' || i.table_name2 || ' (' || i.pk || ' )';
      p_insert_log(p_tab,
                   'p_deal_constraint',
                   v_sql_p_constraint,
                   '将新分区表的外键加上',
                   10,
                   2);
      p_if_judge(v_sql_p_constraint, p_deal_flag);
      dbms_output.put_line('操作步骤10(处理外键)------对' || p_tab || '表的' ||
                           i.column_
                           name1 || '列增加外键' || i.constraint_name1);
    end loop;
  else
    dbms_output.put_line('操作步骤10(处理外键)------' || upper(p_tab) || '_' ||
                         yyyymmdd || '并没有外键!');
  end if;
end p_constraint_010;