Oracle 普通表转分区表

1.创建一张临时表,添加日期字段



alter table bp_linkmsg_tb add(tran_date date  default SYSDATE);


2. 修改日期字段数据:

2.1 数据量较少时:
update P_BP_LINKMSG_TB t set t.tran_date=to_date(substr(t.task_id,7,8),'yyyymmdd') where t.task_id like '217010%';

2.2 数据量较大时:

DECLARE
CURSOR cur IS
select rowid as ROW_ID from bp_linkmsg_tb t where t.task_id like '217010%'
/*where a.logdate <= to_date('2018-01-01 00:00:00', 'yyyy-mm-dd Hh24:mi:ss')*/
order by rowid;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
update bp_linkmsg_tb t set t.tran_date=to_date(substr(t.task_id,7,8),'yyyymmdd')
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;

3. 创建分区表
create table p_bp_linkmsg_tb
(
  TASK_ID   VARCHAR2(50) not null,
  FLOW_NODE VARCHAR2(4) not null,
  LINKMSG   CLOB default '',
  TRAN_DATE DATE default SYSD