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 SYSDATE,
constraint p_bp_linkmsg_tb_pk primary key (TASK_ID, FLOW_NODE)
)
partition by range (TRAN_DATE)
(
partition p_bp_linkmsg_tb_20161130 values less than ((to_date('2016-12-01','yyyy-mm-dd')))
);
4.利用python 批量生成分区
# !/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
from datetime import datetime
import cx_Oracle
import os
import sys
import re
import time
reload(sys)
sys.setdefaultencoding('utf-8')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
import datetime # µ¼ÈÈÆʱ¼ä¿éable_name = 'T_PM_ACCT_DTL_AF'
table_name = 'p_bp_linkmsg_tb'
start_date = '20161130'
stop_date = '20181231'
print table_name
print start_date
print stop_date
conn = cx_Oracle.connect('tlcbuser/tlcbuser@20.5.101.31/tlyy')
tips = '+1'
def get_date(v_date):
cur = conn.cursor()
msql = "select to_char((to_date(%s,'YYYYMMDD')%s),'YYYY-MM-DD') from dual where rownum=1"% (v_date, tips)
# print msql
cur.execute(msql)
row = cur.fetchone()
# print row
return row[0]
sdate = start_date
stop_date = get_date(stop_date)
stop_date = stop_date.replace('-', '')
while True:
xdate = get_date(sdate)
ydate = xdate.replace('-', '')
# print xdate
# print ydate
ndate = xdate
psql = "alter table %s add partition %s_%s values less than (to_date('%s','yyyy-mm-dd'));"% ( table_name, table_name, sdate, ndate)
sdate = ydate
if ydate == stop_date:
break;
print psql
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161130 values less than (to_date('2016-12-01','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161201 values less than (to_date('2016-12-02','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161202 values less than (to_date('2016-12-03','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161203 values less than (to_date('2016-12-04','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161204 values less than (to_date('2016-12-05','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161205 values less than (to_date('2016-12-06','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161206 values less than (to_date('2016-12-07','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition p_bp_linkmsg_tb_20161207 values less than (to_date('2016-12-08','yyyy-mm-dd'));
alter table p_bp_linkmsg_tb add partition pmax values less than (to_date('2999-12-31','yyyy-mm-dd'));
pmax 作用:
为了防止没有最大值，数据库没法insert
分区表已经建成,查看分区表信息:
SQL> select TABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from user_part_tables;
TABLE_NAME PARTITION SUBPARTIT
------------------------------ --------- ---------
BIN$e976FpCHDtvgU3sQBAovCQ==$0 RANGE NONE
P_BP_LINKMSG_TB RANGE NONE
TEST_INTERVAL_LINKMSG_TB RANGE NONE
5.开始重定义操作
5.1 检查重定义合理性
P_BP_LINKMSG_TB
P_BP_LINKMSG_TB
SQL> exec dbms_redefinition.can_redef_table('SUNDS','P_BP_LINKMSG_TB');
BEGIN dbms_redefinition.can_redef_table('SUNDS','P_BP_LINKMSG_TB'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "SUNDS"."P_BP_LINKMSG_TB" with no
primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627
ORA-06512: at line 1
SQL> alter table P_BP_LINKMSG_TB add constraint pk_id primary key(TASK_ID, FLOW_NODE);
Table altered.
SQL> exec dbms_redefinition.can_redef_table('SUNDS','P_BP_LINKMSG_TB');
PL/SQL procedure successfully completed.
5.2 如果没有问题,开始重定义,这个过程可能要等一会
SQL>
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SUNDS',
orig_table => 'BP_LINKMSG_TB',
int_table => 'P_BP_LINKMSG_TB');
end;
2 3 4 5 6 7
8 /
PL/SQL procedure successfully completed.
SQL>
这一步操作结束后，数据就已经同步到这个临时的分区表里来了。
5.3
此时的P_BP_LINKMSG_TB 是一个物化视图
CREATE MATERIALIZED VIEW P_BP_LINKMSG_TB
ON PREBUILT TABLE
REFRESH FAST ON DEMAND
AS
SELECT "P_BP_LINKMSG_TB"."TASK_ID" "TASK_ID","P_BP_LINKMSG_TB"."FLOW_NODE" "FLOW_NODE","P_BP_LINKMSG_TB"."LINKMSG" "LINKMSG","P_BP_LINKMSG_TB"."TRAN_DATE" "TRAN_DATE" FROM "SUNDS"."P_BP_LINKMSG_TB" "P_BP_LINKMSG_TB";
5.4 同步新表，这是可选的操作
SQL> BEGIN
2 dbms_redefinition.sync_interim_table(
3 uname => 'SUNDS',
4 orig_table => 'BP_LINKMSG_TB',
5 int_table => 'P_BP_LINKMSG_TB');
6 END;
7 /
PL/SQL 过程已成功完成
5.5 结束重定义
SQL>
BEGIN
dbms_redefinition.finish_redef_table(uname => 'SUNDS',
orig_table => 'BP_LINKMSG_TB',
int_table => 'P_BP_LINKMSG_TB');
END;SQL> 2 3 4 5
6 /
PL/SQL procedure successfully completed.
此时BP_LINKMSG_TB已变为分区表