在线把普通表进行分区

一张日志表膨胀到了4k万，占用了30G的空间，决定按照季度进行分区。

整个过程大约用了半个小时（包括建立全局分区索引）。在线重定义的速度还不错，下面是记录下来的脚本。

 

--进行在线把his_worksheet_routelog表进行分区
set serveroutput on size 10000;

drop table   his_WORKSHEET_ROUTELOG_bak;
create table his_WORKSHEET_ROUTELOG_bak
(
  logid            NUMBER(18) not null,
  operatetime      DATE not null,
  staffid          NUMBER(18),
  staffcode        VARCHAR2(50),
  staffname        VARCHAR2(50),
  accessid         NUMBER(20),
  worksheetid      NUMBER(18),
  pxmode           NUMBER(1),
  routeinfo        CLOB,
  logtype          VARCHAR2(20),
  exceptioncode    VARCHAR2(50),
  isauto           NUMBER(1),
  nodecode         VARCHAR2(20),
  typecode         VARCHAR2(250),
  serviceid        VARCHAR2(60),
  new_routeid      NUMBER(20),
  old_routeid      NUMBER(20),
  config_line_type NUMBER(3),
  memo             VARCHAR2(255),
  operate_reason   VARCHAR2(20)
)  lob(routeinfo) store as (tablespace DAT_LOB)
   PARTITION BY RANGE (operatetime)(
   PARTITION P_wslog_2010_1 VALUES LESS THAN (TO_DATE('2010-04-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2010_2 VALUES LESS THAN (TO_DATE('2010-07-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2010_3 VALUES LESS THAN (TO_DATE('2010-10-01', 'YYYY-MM-DD')), 
   PARTITION P_wslog_2010_4 VALUES LESS THAN (TO_DATE('2011-01-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2011_1 VALUES LESS THAN (TO_DATE('2011-04-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2011_2 VALUES LESS THAN (TO_DATE('2011-07-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2011_3 VALUES LESS THAN (TO_DATE('2011-10-01', 'YYYY-MM-DD')), 
   PARTITION P_wslog_2011_4 VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2012_1 VALUES LESS THAN (TO_DATE('2012-04-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2012_2 VALUES LESS THAN (TO_DATE('2012-07-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2012_3 VALUES LESS THAN (TO_DATE('2012-10-01', 'YYYY-MM-DD')), 
   PARTITION P_wslog_2012_4 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2013_1 VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2013_2 VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
   PARTITION P_wslog_2013_3 VALUES LESS THAN (TO_DATE('2013-10-01', 'YYYY-MM-DD')), 
   PARTITION P_wslog_2013_4 VALUES LESS THAN (TO_DATE('2014-01-01', 'YYYY-MM-DD'))                     
);

alter table his_WORKSHEET_ROUTELOG_bak
  add constraint PK_his_WORKSHEET_ROUTELOG_bak primary key (LOGID);
 
whenever sqlerror exit;

begin
  DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,
                                    upper('his_worksheet_routelog'),
                                    DBMS_REDEFINITION.CONS_USE_PK);
  DBMS_REDEFINITION.START_REDEF_TABLE(USER,
                                      upper('his_worksheet_routelog'),
                                      upper('his_worksheet_routelog_bak'));
  dbms_redefinition.sync_interim_table(USER,
                                       upper('his_worksheet_routelog'),
                                       upper('his_worksheet_routelog_bak'));
  dbms_redefinition.FINISH_REDEF_TABLE(USER,
                                       upper('his_worksheet_routelog'),
                                       upper('his_worksheet_routelog_bak'));
exception
  when others then
  begin
    dbms_redefinition.abort_redef_table(USER,
                                       upper('his_worksheet_routelog'),
                                       upper('his_worksheet_routelog_bak'));
    raise;                                
  end;                                        
end;
/
whenever sqlerror continue;

alter index IDX_his_WORKSHEET_ROUTELOG_1 rename to IDX_his_WORKSHEET_LOG_bak_1;
alter index IDX_his_WORKSHEET_ROUTELOG_2 rename to IDX_his_WORKSHEET_LOG_bak_2;
alter index IDX_his_WORKSHEET_ROUTELOG_3 rename to IDX_his_WORKSHEET_LOG_bak_3;
alter index IDX_his_WORKSHEET_ROUTELOG_4 rename to IDX_his_WORKSHEET_LOG_bak_4;
alter index PK_his_WORKSHEET_ROUTELOG rename to PK_his_WORKSHEET_ROUTELOG_bak2;
alter table HIS_WORKSHEET_ROUTELOG_bak drop primary key cascade keep index;
alter table HIS_WORKSHEET_ROUTELOG_bak add constraint PK_his_WORKSHEET_ROUTELOG_bak2 primary key(LOGID);

alter index PK_HIS_WORKSHEET_ROUTELOG_BAK rename to PK_HIS_WORKSHEET_ROUTELOG;
alter table HIS_WORKSHEET_ROUTELOG drop primary key cascade keep index;
alter table HIS_WORKSHEET_ROUTELOG add constraint PK_his_WORKSHEET_ROUTELOG primary key(LOGID);

--
---创建分区索引
create index idx_his_worksheet_routelog_1 on his_worksheet_routelog(operatetime) local;
create index idx_his_worksheet_routelog_2 on his_worksheet_routelog(serviceid) global partition  by hash(serviceid) partitions 20;
create index idx_his_worksheet_routelog_3 on his_worksheet_routelog(worksheetid) global partition  by hash(worksheetid) partitions 20;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-678646/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-678646/