海量資料處理_表分割槽(線上重定義)

使用線上重定義方式將表分割槽

1,許可權：
execute_catalog_role:execute on dbms_redefinition
create any table
alter any table
drop any table
lock any table
select any table

2,確定是否可以重定義
BEGIN
  dbms_redefinition.can_redef_table(uname        => USER,
                                    tname        => 'ACT_LOG',
                                    options_flag => dbms_redefinition.cons_use_rowid,
                                    part_name    => NULL);
END;
/

3,建立臨時表
create table ACT_LOG_PART
(
  USER_ID     INTEGER not null,
  ACTION_DATE TIMESTAMP(6) not null,
  TYPE        VARCHAR2(50 BYTE) not null,
  DETAIL      VARCHAR2(4000 BYTE) not null,
  CHNL        VARCHAR2(1 BYTE) default '1'
) partition by range(action_date)(

partition p200909 values less than (to_date('20091001','YYYYMMDD')),
partition p201011 values less than (to_date('20101201','YYYYMMDD')),
partition p201201 values less than (to_date('20120201','YYYYMMDD')),
partition p201202 values less than (to_date('20120301','YYYYMMDD')),
partition p201203 values less than (to_date('20120401','YYYYMMDD')),
partition p201204 values less than (to_date('20120501','YYYYMMDD')),
partition p201205 values less than (to_date('20120601','YYYYMMDD')),
partition p201206 values less than (to_date('20120701','YYYYMMDD')),
partition p201207 values less than (to_date('20120801','YYYYMMDD')),
partition p201210 values less than (to_date('20121101','YYYYMMDD')),
partition p201211 values less than (to_date('20121201','YYYYMMDD')),
partition p201212 values less than (to_date('20130101','YYYYMMDD')),
partition p201303 values less than (to_date('20130401','YYYYMMDD')),
partition p201410 values less than (to_date('20141101','YYYYMMDD')),
partition p201412 values less than (to_date('20150101','YYYYMMDD')),
partition p201506 values less than (to_date('20150701','YYYYMMDD')),
partition p201507 values less than (to_date('20150801','YYYYMMDD')),
partition p201508 values less than (to_date('20150901','YYYYMMDD')),
partition p201509 values less than (to_date('20151001','YYYYMMDD')),
partition p201510 values less than (to_date('20151101','YYYYMMDD')),
partition p201601 values less than (to_date('20160201','YYYYMMDD')),
partition p201602 values less than (to_date('20160301','YYYYMMDD')),
partition p201604 values less than (to_date('20160501','YYYYMMDD')),
partition pmax values less than (maxvalue)
);

4,開始重定義
alter session force parallel dml parallel 4;
alter session force parallel ddl parallel 4;

BEGIN
  dbms_redefinition.start_redef_table(uname        => USER,
                                      orig_table   => 'ACT_LOG',
                                      int_table    => 'ACT_LOG_PART',
                                      col_mapping  => NULL,
                                      options_flag => dbms_redefinition.cons_use_rowid,
                                      orderby_cols => NULL,
                                      part_name    => NULL);
END;
/
 
5,建立索引
--create index
create index IDX_ACT_LOG_USERID_DATE on ACT_LOG_PART (USER_ID, ACTION_DATE) local;

6,同步
alter session disable parallel dml;
alter session disable parallel ddl;

BEGIN
  dbms_redefinition.sync_interim_table(uname      => USER,
                                       orig_table => 'ACT_LOG',
                                       int_table  => 'ACT_LOG_PART',
                                       part_name  => NULL);
END;
/

7,完成
BEGIN
  dbms_redefinition.finish_redef_table(uname      => USER,
                                       orig_table => 'ACT_LOG',
                                       int_table  => 'ACT_LOG_PART',
                                       part_name  => NULL);
END;
/

8,刪除臨時表
rename act_log_part to act_log_bak;

9,分析
begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'ACT_LOG');
end;
/