利用ORACLE DBMS_REDEFINITION包进行普通表到分区表的在线转换
在生产系统中，由于设计上的原因，一些数据量很大的表需要转换成分区表，以提高性能。而oracle自带的DBMS_REDEFINITION包能完成普通表到分区表的在线重定义。与其它手工转换的方式相比，该方法能很容易保证数据的一致性，并最大限度的减少对生产的影响。其内部实现机制是首先创建物化视图记录原表数据的变更，再将原表数据加载到目标表中，加载完成后，可以灵活的手工进行变更数据的同步，最后通过调用FINISH_REDEF_TABLE过程，对中间表和原表进行名字互换，完成转换。需要注意的是，如果表较大，比如超过10G的表，在转换的过程中可能会引起频繁的redo的切换，所以在转换前需要将中间表改为nologging,转换过程中会占用较大的undo表空间，如10G的表，至少需要30G的undo表空间，可以临时创建一个较大的undo表空间设置为系统默认undo表空间，转换完成后，将undo表空间切换回原undo表空间，待临时undo表空间的事务完全提交后，将其删除。
以下以某生产系统中的转换过程为例说明，该系统中有三张普通表需要转换为分区表，分区类型为range分区，一个月一个分区，除了主键以外，其它索引都创建为local.方便后续分区表的数据清理维护。其中一张表需要增加一列时间类型的字段，并将varchar2类型的时间内容数据更新至该字段。该字段同时为分区关键字。以下是具体的实施步骤：
1.检查执行DBMS_REDEFINITION包的用户的权限。
要执行DBMS_REDEFINITION，需要相关权限，可用下面命令完成授权：
GRANT Create any table TO MM_PPMS;
GRANT Alter any table TO MM_PPMS;
GRANT Drop any table TO MM_PPMS;
GRANT Lock any table TO MM_PPMS;
GRANT Select any table TO MM_PPMS;
2. 创建表空间indx_mm_ppms，用来分离分区表的索引和数据
如果表预期比较大的话，最好将每张表放到单独的表空间，方便管理。
CREATE TABLESPACE INDX_MM_PPMS DATAFILE
'/dev/vg_cora1/rdata_1g_258' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_261' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_264' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_267' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_270' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_273' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_276' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_279' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_282' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_285' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_288' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_291' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_297' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_300' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_232' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_235' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_244' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_247' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_250' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_253' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_256' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_259' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_262' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_265' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_268' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_271' SIZE 1023M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 2048K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
3.检查数据库该用户的无效对象情况并作好记录。
可以写一个简单的shell脚本来完成这部分的检查，脚本名为：check_invalid_objects.sh，内容如下：
sqlplus "/as sysdba"<
set heading off
set echo off
set feedback off
col owner for a12
set lines 400
select 'Current check database is :'||sys_context('userenv','db_name') from dual;
select 'Invalid object number is :'||count(*) from dba_objects where status='INVALID' and wner='MM_PPMS';
select 'Invalid objects are :'||owner,object_type,object_name from dba_objects where status='INVALID' and wner='MM_PPMS';
exit;
EOF
4. 创建中间分区表及相关索引
该分区表在转换为最后一步会rename成生产表的名字，下面分别创建三张分区表及索引，索引参照原表的命名，以_P结尾，由于表比较大，所以需要修改原表的相关参数：CDR_INT，ONDEMAND_ORDER_HIST_INT，ONDEMAND_PUSH_INT。
-- Create table CDR_INT
create table CDR_INT
(
CDRSEQ VARCHAR2(12) not null,
FEEUSERID VARCHAR2(15) not null,
DESTUSERID VARCHAR2(15) not null,
OSPCODE VARCHAR2(6) not null,
SERVICECODE VARCHAR2(10) not null,
CHARGETYPE VARCHAR2(2) not null,
FEE NUMBER(8) not null,
CDRTYPE VARCHAR2(2) not null,
ACCESSMODEID VARCHAR2(2) not null,
CDRTIME VARCHAR2(14) not null,
CONTENTCODE VARCHAR2(10),
CPCODE VARCHAR2(6),
CONTENTTIME VARCHAR2(14),
STATUS NUMBER(2) default 0 not null,
CHARGEFLAG NUMBER(1),
ORDERNUMBER VARCHAR2(20),
PAYWAY NUMBER(4),
PAYACCOUNTS VARCHAR2(40),
CREATETIME DATE
)
tablespace DATA_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(CREATETIME)
(
PARTITION T_CDR_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_CDR_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_CDR_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_CDR_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_CDR_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_CDR_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_CDR_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_CDR_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_CDR_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_CDR_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_CDR_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_CDR_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_CDR_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_CDR_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_CDR_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_CDR_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_CDR_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_CDR_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_CDR_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_CDR_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_CDR_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_CDR_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_CDR_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_CDR_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_CDR_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_CDR_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_CDR_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CDR_INT
add constraint PK_CDR_P primary key (CDRSEQ)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index IDX_CDR_CDRSEQ_P on CDR_INT (TO_NUMBER(CDRSEQ))
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_CDR_CREATETIME_P on CDR_INT (CREATETIME)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create table
create table ONDEMAND_ORDER_HIST_INT
(
HISTID NUMBER(10) not null,
ORDERNUMBER VARCHAR2(20) not null,
USERID VARCHAR2(15) not null,
PRODUCTID VARCHAR2(12) not null,
SPCODE VARCHAR2(6) not null,
SERVICECODE VARCHAR2(10) not null,
ACTIONTYPE NUMBER(2),
ACTIONDATE DATE not null,
STARTDATE DATE not null,
LUPDDATE DATE,
CHARGESTATUS CHAR(1) not null,
ORDERSTATUS CHAR(1),
CHARGEUSERID VARCHAR2(15) not null,
ORDERPLACE VARCHAR2(2),
PACKAGEID VARCHAR2(12),
ACCESSMODEID VARCHAR2(2),
PAYWAY NUMBER(1) not null,
PRODUCTCODE VARCHAR2(39),
CONTENTID VARCHAR2(12),
ORDERTYPE NUMBER(1),
EXPIRETIME DATE,
VALIDTIMES NUMBER(3),
PRICE NUMBER(8),
CPCODE VARCHAR2(10),
DOWNLOADFLAG NUMBER(8) default 0,
SALESCHANNELID VARCHAR2(64),
ORDERFLAG NUMBER(1),
PAYACCOUNTS VARCHAR2(40)
)
tablespace DATA_PPMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(ACTIONDATE)
(
PARTITION T_ORDER_HIST_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_ORDER_HIST_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_ORDER_HIST_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_ORDER_HIST_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_ORDER_HIST_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_ORDER_HIST_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_ORDER_HIST_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_ORDER_HIST_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_ORDER_HIST_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_ORDER_HIST_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_ORDER_HIST_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_ORDER_HIST_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_ORDER_HIST_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_ORDER_HIST_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_ORDER_HIST_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_ORDER_HIST_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_ORDER_HIST_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_ORDER_HIST_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_ORDER_HIST_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_ORDER_HIST_INT
add constraint PK_ONDEMAND_ORDER_HIST_P primary key (HISTID)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index IDX_ON_ORDER_HIST_USERID_P on ONDEMAND_ORDER_HIST_INT (USERID,PRODUCTID)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_CHARGEUSERID_P on ONDEMAND_ORDER_HIST_INT (CHARGEUSERID)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_ORDERNUMBER_P on ONDEMAND_ORDER_HIST_INT (ORDERNUMBER)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_ACTIONDATE_P on ONDEMAND_ORDER_HIST_INT (ACTIONDATE)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create table
create table ONDEMAND_PUSH_INT
(
ORDERNUMBER VARCHAR2(20) not null,
USERID VARCHAR2(15) not null,
PRODUCTID VARCHAR2(12) not null,
ENDTIME DATE,
UA VARCHAR2(200),
STARTTIME DATE not null,
PARAMS VARCHAR2(512),
PUSHID VARCHAR2(30) not null,
REPORTSTATUS VARCHAR2(4),
ERRORDESC VARCHAR2(100),
CONTENTID VARCHAR2(12),
FEEMSISDN VARCHAR2(15),
DOWNLOADTIMES NUMBER(8),
CPCODE VARCHAR2(10),
VALIDRPTTIMES NUMBER(8) default 1,
DOWNCHANNELID VARCHAR2(2) default '10'
)
tablespace DATA_PPMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(STARTTIME)
(
PARTITION T_OND_PUSH_P200908 VALUES LESS THAN (to_date('20090901','yyyymmdd')),
PARTITION T_OND_PUSH_P200909 VALUES LESS THAN (to_date('20091001','yyyymmdd')),
PARTITION T_OND_PUSH_P200910 VALUES LESS THAN (to_date('20091101','yyyymmdd')),
PARTITION T_OND_PUSH_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_OND_PUSH_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_OND_PUSH_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_OND_PUSH_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_OND_PUSH_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_OND_PUSH_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_OND_PUSH_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_OND_PUSH_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_OND_PUSH_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_OND_PUSH_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_OND_PUSH_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_OND_PUSH_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_OND_PUSH_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_OND_PUSH_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_OND_PUSH_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_OND_PUSH_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_OND_PUSH_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_OND_PUSH_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_OND_PUSH_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_OND_PUSH_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_OND_PUSH_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_OND_PUSH_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_OND_PUSH_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_OND_PUSH_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_OND_PUSH_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_OND_PUSH_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_OND_PUSH_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_PUSH_INT
add constraint PK_ONDEMAND_PUSH_P primary key (PUSHID)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index INX_OP_ORDERNUMBER_P on ONDEMAND_PUSH_INT (ORDERNUMBER)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index INX_OP_STARTTIME_P on ONDEMAND_PUSH_INT (STARTTIME)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
5.将分区表的表及索引改为nologging，减少redo的切换频率
这一步也可以在创建索引和表的时候直接指定。
alter index INX_OP_ORDERNUMBER_P nologging;
alter index INX_OP_STARTTIME_P nologging;
alter index PK_ONDEMAND_PUSH_P nologging;
alter index ONDEMAND_ORDER_HIST_INT nologging;
alter index IDX_ON_ORDER_HIST_USERID_P nologging;
alter index IDX_OOH_CHARGEUSERID_P nologging;
alter index IDX_OOH_ORDERNUMBER_P nologging;
alter index IDX_OOH_ACTIONDATE_P nologging;
alter table mm_ppms.ONDEMAND_PUSH_INT nologging;
alter table mm_mmpms.ONDEMAND_ORDER_HIST_INT nologging;
alter table mm_mmps.CDR_INT nologging;
6.由脚本call.sh调用start_redef_table.sql开始转换分区。
由于分区转换的时间可能比较长，所以最好由shell脚本调用sql脚本，通过nohup的方式后台执行。在分区转换的过程中，要密切监视undo表空间的使用情况。
start_redef_table.sql脚本的内容如下：
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
查看过程PROCEDURE START_REDEF_TABLE的参数情况如下，我们只需要指定参数UNAME，ORIG_TABLE，INT_TABLE即可。
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
一个简单的call.sh的脚本如下：
#!/bin/ksh
# $Header: call.sh 6-aug-2010.11:56 zhao,lehuan $
# call.sh
# Copyright (c) Aspire. All rights reserved.
# NAME
# call.sh -
# DESCRIPTION
#
# MODIFIED (MM/DD/YY)
#set -x
if [ $# -lt 1 ]
then
echo "Usage:$0 "
exit 1
fi
SCRIPT_NAME=$1
DB_ID=mmcore
. ~oracle/.profile
##############################################
#Set work dir
WORKPATH=/oracle/utils/partition
##############################################
#Set DATE format
NOWDATE=`date '+%Y%m%d'`
NOWTIME=`date '+%Y-%m-%d-%H:%M'`
################################################
#Set mail recipients and subject
MAILLIST="zhaolehuan@aspire-tech.com"
MAILSUBJECT="call_script_$SCRIPT_NAME_$NOWTIME"
###################################
##############################################
#Load passwd file
#/oracle/utils/orapwdtab format:
#$DB_ID:$USERNAME:$PASSWD
PASSWDFILE=${WORKPATH}/.orapwdtab
if [ ! -f ${PASSWDFILE} ]
then
cp /oracle/utils/orapwdtab ${WORKPATH}/.orapwdtab
chmod 600 ${WORKPATH}/.orapwdtab
fi
if [ ! -f ${PASSWDFILE} ]
then
echo "Can not find password file ${PASSWDFILE}"
exit
fi
USERNAME=mm_ppms
PASSWD=`grep -v "^[#]" ${PASSWDFILE}|grep -i ${DB_ID}|grep -i ${USERNAME}| cut -d: -f3`
cd ${WORKPATH}
###########################################################################
sqlplus -s ${USERNAME}/${PASSWD}@${DB_ID} <
set echo off
set feedback on
set echo on
set trimspool on
set heading on
set timing on
set time on
spool ${WORKPATH}/${SCRIPT_NAME}.file
/* add sql script*/
@ SCRIPT_NAME
spool off
exit;
eof
cat ${WORKPATH}/${SCRIPT_NAME}.file
mailx -m -s ${MAILSUBJECT} -r oracle@`hostname`.com $MAILLIST <${SCRIPT_NAME}.file
exit
7.通过调用脚本sync_interim_table.sql来进行同步数据
如果开始转换和结束转换之间的时间比较长，要么，在这期间可以通过调用上述脚本来进行，SYNC_INTERIM_TABLE这个程序的参数调用与start的调用是一样的。
sync_interim_table.sql脚本的内容如下：
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
8. 由脚本call.sh调用finish_redef_table.sql结束分区转换。
同步完成后，便可以结束分区转换，一般来说，这个过程很快就结束，转换结束后，原普通表的表名事实上对应新创建的分区表，分区表的表名对应原普通表。
finish_redef_table.sql脚本的内容如下，调用参数参见以上说明。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
9.对转换完毕的分区表进行表分析，收集统计信息
由于是生产系统，可以采用较低的采样比迅速完成分析，然后再在系统非高峰期的时候采用恰当的采样比对上述表进行一次表分析。
分析脚本内容如下：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'CDR',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_ORDER_HIST',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_PUSH',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
10. 将三张表的表及索引改为logging的状态
logging;
alter index PK_ONDEMAND_PUSH_P logging;
alter index ONDEMAND_ORDER_HIST_INT logging;
alter index IDX_ON_ORDER_HIST_USERID_P logging;
alter index IDX_OOH_CHARGEUSERID_P logging;
alter index IDX_OOH_ORDERNUMBER_P logging;
alter index IDX_OOH_ACTIONDATE_P logging;
alter table ONDEMAND_PUSH logging;
alter table ONDEMAND_ORDER_HIST logging;
alter table CDR logging;
11.检查数据库无效对象，并进行编译，参考如下的脚本进行编译：
sqlplus /nolog <
conn / as sysdba
---recompile all
--@$ORACLE_HOME/rdbms/admin/utlrp.sql;
---compile mocsacct
EXEC DBMS_UTILITY.COMPILE_SCHEMA('MM_PPMS',TRUE);
exit;
EOF
12.后续临时表清理
数据验证无误后，将三张普通表（ONDEMAND_ORDER_HIST_INT，CDR_INT，ONDEMAND_PUSH_INT
）从数据库中删除。
以上的操作过程中，开始转换分区三张表一共花了2个小时左右，这三张表的数据量总数在20G左右，为了减少对undo表空间的争用，三张表依次进行转换，同步的过程很快，在10分钟内就同步完成，结束转换的操作不到两分钟就完成了。而整个操作对生产系统基本无影响，从业务上感觉不到整个转换的过程。
附：CDR表新加列的操作
在CDR表的转换之前，由于分区键createtime原表上是没有的，需要创建新的一列，并将cdrtime列的内容转换成日期格式后更新到新的列中，由于cdr表的数据量在3000多万条，所以只能将此更新分割为较小的事务进行操作，该更新通过编写一个procedure来完成，整个操作步骤如下所示：
整个操作还是由call.sh调用add_newcolumn_on_cdr.sql来完成，add_newcolumn_on_cdr.sql的内容如下：
alter table mm_ppms.cdr add(createtime date);
alter table mm_ppms.cdr modify(createtime date default sysdate);
exec zlh_update_cdr(100000);
ALTER TABLE MM_PPMS.CDR MODIFY CREATETIME NOT NULL;
上述脚本中的过程exec zlh_update_cdr的功能为更新cdrtime的内容到createtime列，参数为提交的频率，如上即为10W行记录一次提交。代码如下：
create or replace procedure mm_ppms.zlh_update_cdr( interval number)
is
v_maxvalue number;
v_minvalue number;
v_range number;
v_interval number;
v_date varchar2(30);
v_looptime number;
v_l_cdrseq number;
v_h_cdrseq number;
errid number;
/*
create table mm_ppms.zlh_cdr_log(
label varchar2(30),
description varchar2(500),
logtime date
)
*/
cursor cur_cdr is select min(TO_NUMBER(CDRSEQ)) minv,
max(TO_NUMBER(CDRSEQ)) maxv,
max(TO_NUMBER(CDRSEQ))- min(TO_NUMBER(CDRSEQ)) rangev
from mm_ppms.cdr;
begin
/*
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
*/
select to_char(sysdate,'yyyymmddhh24miss') into v_date from dual;
v_interval:= interval;
for rec in cur_cdr LOOP
begin
v_minvalue:=rec.minv;
v_maxvalue:=rec.maxv;
v_range:=rec.rangev;
end;
end loop;
v_looptime:=trunc(v_range/interval,0)+1;
/*begin update table cdr */
INSERT INTO zlh_cdr_log
VALUES(v_date,'begin update cdr !',sysdate);
v_l_cdrseq:=v_minvalue;
v_h_cdrseq:=v_minvalue+v_interval;
for i in 1 .. v_looptime loop
update mm_ppms.cdr set createtime=to_date(cdrtime,'yyyymmddhh24miss')
where TO_NUMBER(CDRSEQ) between v_l_cdrseq and v_h_cdrseq;
commit;
insert into zlh_cdr_log values(v_date,'update '||v_interval||' rows of table cdr:looptime:'||i||',current cdrseq range is:'||v_h_cdrseq||',mixvalue:'||v_minvalue||'. maxvalue:'||v_maxvalue||'.',sysdate);
commit;
v_l_cdrseq:=v_h_cdrseq+1;
if v_l_cdrseq+v_interval
v_h_cdrseq:=v_l_cdrseq+v_interval;
else
v_h_cdrseq:=v_maxvalue;
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
errid:=SQLCODE;
INSERT INTO zlh_cdr_log
VALUES(v_date,'error:ERRID ='||TO_CHAR(ERRID),sysdate);
commit;
INSERT INTO zlh_cdr_log
VALUES(v_date,'update cdr completed!',sysdate);
commit;
end;
/
1.检查执行DBMS_REDEFINITION包的用户的权限。
要执行DBMS_REDEFINITION，需要相关权限，可用下面命令完成授权：
GRANT Create any table TO MM_PPMS;
GRANT Alter any table TO MM_PPMS;
GRANT Drop any table TO MM_PPMS;
GRANT Lock any table TO MM_PPMS;
GRANT Select any table TO MM_PPMS;
2. 创建表空间indx_mm_ppms，用来分离分区表的索引和数据
如果表预期比较大的话，最好将每张表放到单独的表空间，方便管理。
CREATE TABLESPACE INDX_MM_PPMS DATAFILE
'/dev/vg_cora1/rdata_1g_258' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_261' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_264' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_267' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_270' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_273' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_276' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_279' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_282' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_285' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_288' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_291' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_297' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora1/rdata_1g_300' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_232' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_235' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_244' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_247' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_250' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_253' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_256' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_259' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_262' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_265' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_268' SIZE 1023M AUTOEXTEND OFF,
'/dev/vg_cora2/rdata_1g_271' SIZE 1023M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 2048K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
3.检查数据库该用户的无效对象情况并作好记录。
可以写一个简单的shell脚本来完成这部分的检查，脚本名为：check_invalid_objects.sh，内容如下：
sqlplus "/as sysdba"<
set heading off
set echo off
set feedback off
col owner for a12
set lines 400
select 'Current check database is :'||sys_context('userenv','db_name') from dual;
select 'Invalid object number is :'||count(*) from dba_objects where status='INVALID' and wner='MM_PPMS';
select 'Invalid objects are :'||owner,object_type,object_name from dba_objects where status='INVALID' and wner='MM_PPMS';
exit;
EOF
4. 创建中间分区表及相关索引
该分区表在转换为最后一步会rename成生产表的名字，下面分别创建三张分区表及索引，索引参照原表的命名，以_P结尾，由于表比较大，所以需要修改原表的相关参数：CDR_INT，ONDEMAND_ORDER_HIST_INT，ONDEMAND_PUSH_INT。
-- Create table CDR_INT
create table CDR_INT
(
CDRSEQ VARCHAR2(12) not null,
FEEUSERID VARCHAR2(15) not null,
DESTUSERID VARCHAR2(15) not null,
OSPCODE VARCHAR2(6) not null,
SERVICECODE VARCHAR2(10) not null,
CHARGETYPE VARCHAR2(2) not null,
FEE NUMBER(8) not null,
CDRTYPE VARCHAR2(2) not null,
ACCESSMODEID VARCHAR2(2) not null,
CDRTIME VARCHAR2(14) not null,
CONTENTCODE VARCHAR2(10),
CPCODE VARCHAR2(6),
CONTENTTIME VARCHAR2(14),
STATUS NUMBER(2) default 0 not null,
CHARGEFLAG NUMBER(1),
ORDERNUMBER VARCHAR2(20),
PAYWAY NUMBER(4),
PAYACCOUNTS VARCHAR2(40),
CREATETIME DATE
)
tablespace DATA_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(CREATETIME)
(
PARTITION T_CDR_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_CDR_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_CDR_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_CDR_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_CDR_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_CDR_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_CDR_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_CDR_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_CDR_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_CDR_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_CDR_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_CDR_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_CDR_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_CDR_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_CDR_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_CDR_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_CDR_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_CDR_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_CDR_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_CDR_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_CDR_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_CDR_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_CDR_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_CDR_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_CDR_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_CDR_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_CDR_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CDR_INT
add constraint PK_CDR_P primary key (CDRSEQ)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index IDX_CDR_CDRSEQ_P on CDR_INT (TO_NUMBER(CDRSEQ))
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_CDR_CREATETIME_P on CDR_INT (CREATETIME)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create table
create table ONDEMAND_ORDER_HIST_INT
(
HISTID NUMBER(10) not null,
ORDERNUMBER VARCHAR2(20) not null,
USERID VARCHAR2(15) not null,
PRODUCTID VARCHAR2(12) not null,
SPCODE VARCHAR2(6) not null,
SERVICECODE VARCHAR2(10) not null,
ACTIONTYPE NUMBER(2),
ACTIONDATE DATE not null,
STARTDATE DATE not null,
LUPDDATE DATE,
CHARGESTATUS CHAR(1) not null,
ORDERSTATUS CHAR(1),
CHARGEUSERID VARCHAR2(15) not null,
ORDERPLACE VARCHAR2(2),
PACKAGEID VARCHAR2(12),
ACCESSMODEID VARCHAR2(2),
PAYWAY NUMBER(1) not null,
PRODUCTCODE VARCHAR2(39),
CONTENTID VARCHAR2(12),
ORDERTYPE NUMBER(1),
EXPIRETIME DATE,
VALIDTIMES NUMBER(3),
PRICE NUMBER(8),
CPCODE VARCHAR2(10),
DOWNLOADFLAG NUMBER(8) default 0,
SALESCHANNELID VARCHAR2(64),
ORDERFLAG NUMBER(1),
PAYACCOUNTS VARCHAR2(40)
)
tablespace DATA_PPMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(ACTIONDATE)
(
PARTITION T_ORDER_HIST_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_ORDER_HIST_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_ORDER_HIST_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_ORDER_HIST_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_ORDER_HIST_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_ORDER_HIST_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_ORDER_HIST_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_ORDER_HIST_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_ORDER_HIST_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_ORDER_HIST_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_ORDER_HIST_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_ORDER_HIST_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_ORDER_HIST_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_ORDER_HIST_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_ORDER_HIST_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_ORDER_HIST_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_ORDER_HIST_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_ORDER_HIST_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_ORDER_HIST_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_ORDER_HIST_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_ORDER_HIST_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_ORDER_HIST_INT
add constraint PK_ONDEMAND_ORDER_HIST_P primary key (HISTID)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index IDX_ON_ORDER_HIST_USERID_P on ONDEMAND_ORDER_HIST_INT (USERID,PRODUCTID)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_CHARGEUSERID_P on ONDEMAND_ORDER_HIST_INT (CHARGEUSERID)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_ORDERNUMBER_P on ONDEMAND_ORDER_HIST_INT (ORDERNUMBER)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index IDX_OOH_ACTIONDATE_P on ONDEMAND_ORDER_HIST_INT (ACTIONDATE)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create table
create table ONDEMAND_PUSH_INT
(
ORDERNUMBER VARCHAR2(20) not null,
USERID VARCHAR2(15) not null,
PRODUCTID VARCHAR2(12) not null,
ENDTIME DATE,
UA VARCHAR2(200),
STARTTIME DATE not null,
PARAMS VARCHAR2(512),
PUSHID VARCHAR2(30) not null,
REPORTSTATUS VARCHAR2(4),
ERRORDESC VARCHAR2(100),
CONTENTID VARCHAR2(12),
FEEMSISDN VARCHAR2(15),
DOWNLOADTIMES NUMBER(8),
CPCODE VARCHAR2(10),
VALIDRPTTIMES NUMBER(8) default 1,
DOWNCHANNELID VARCHAR2(2) default '10'
)
tablespace DATA_PPMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
)
PARTITION BY RANGE(STARTTIME)
(
PARTITION T_OND_PUSH_P200908 VALUES LESS THAN (to_date('20090901','yyyymmdd')),
PARTITION T_OND_PUSH_P200909 VALUES LESS THAN (to_date('20091001','yyyymmdd')),
PARTITION T_OND_PUSH_P200910 VALUES LESS THAN (to_date('20091101','yyyymmdd')),
PARTITION T_OND_PUSH_P200911 VALUES LESS THAN (to_date('20091201','yyyymmdd')),
PARTITION T_OND_PUSH_P200912 VALUES LESS THAN (to_date('20100101','yyyymmdd')),
PARTITION T_OND_PUSH_P201001 VALUES LESS THAN (to_date('20100201','yyyymmdd')),
PARTITION T_OND_PUSH_P201002 VALUES LESS THAN (to_date('20100301','yyyymmdd')),
PARTITION T_OND_PUSH_P201003 VALUES LESS THAN (to_date('20100401','yyyymmdd')),
PARTITION T_OND_PUSH_P201004 VALUES LESS THAN (to_date('20100501','yyyymmdd')),
PARTITION T_OND_PUSH_P201005 VALUES LESS THAN (to_date('20100601','yyyymmdd')),
PARTITION T_OND_PUSH_P201006 VALUES LESS THAN (to_date('20100701','yyyymmdd')),
PARTITION T_OND_PUSH_P201007 VALUES LESS THAN (to_date('20100801','yyyymmdd')),
PARTITION T_OND_PUSH_P201008 VALUES LESS THAN (to_date('20100901','yyyymmdd')),
PARTITION T_OND_PUSH_P201009 VALUES LESS THAN (to_date('20101001','yyyymmdd')),
PARTITION T_OND_PUSH_P201010 VALUES LESS THAN (to_date('20101101','yyyymmdd')),
PARTITION T_OND_PUSH_P201011 VALUES LESS THAN (to_date('20101201','yyyymmdd')),
PARTITION T_OND_PUSH_P201012 VALUES LESS THAN (to_date('20110101','yyyymmdd')),
PARTITION T_OND_PUSH_P201101 VALUES LESS THAN (to_date('20110201','yyyymmdd')),
PARTITION T_OND_PUSH_P201102 VALUES LESS THAN (to_date('20110301','yyyymmdd')),
PARTITION T_OND_PUSH_P201103 VALUES LESS THAN (to_date('20110401','yyyymmdd')),
PARTITION T_OND_PUSH_P201104 VALUES LESS THAN (to_date('20110501','yyyymmdd')),
PARTITION T_OND_PUSH_P201105 VALUES LESS THAN (to_date('20110601','yyyymmdd')),
PARTITION T_OND_PUSH_P201106 VALUES LESS THAN (to_date('20110701','yyyymmdd')),
PARTITION T_OND_PUSH_P201107 VALUES LESS THAN (to_date('20110801','yyyymmdd')),
PARTITION T_OND_PUSH_P201108 VALUES LESS THAN (to_date('20110901','yyyymmdd')),
PARTITION T_OND_PUSH_P201109 VALUES LESS THAN (to_date('20111001','yyyymmdd')),
PARTITION T_OND_PUSH_P201110 VALUES LESS THAN (to_date('20111101','yyyymmdd')),
PARTITION T_OND_PUSH_P201111 VALUES LESS THAN (to_date('20111201','yyyymmdd')),
PARTITION T_OND_PUSH_P201112 VALUES LESS THAN (to_date('20120101','yyyymmdd')),
PARTITION T_OND_PUSH_PMAX VALUES LESS THAN (MAXVALUE)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ONDEMAND_PUSH_INT
add constraint PK_ONDEMAND_PUSH_P primary key (PUSHID)
using index
global tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index INX_OP_ORDERNUMBER_P on ONDEMAND_PUSH_INT (ORDERNUMBER)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
create index INX_OP_STARTTIME_P on ONDEMAND_PUSH_INT (STARTTIME)
local tablespace INDX_MM_PPMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4096K
next 2048K
minextents 1
maxextents unlimited
pctincrease 0
);
5.将分区表的表及索引改为nologging，减少redo的切换频率
这一步也可以在创建索引和表的时候直接指定。
alter index INX_OP_ORDERNUMBER_P nologging;
alter index INX_OP_STARTTIME_P nologging;
alter index PK_ONDEMAND_PUSH_P nologging;
alter index ONDEMAND_ORDER_HIST_INT nologging;
alter index IDX_ON_ORDER_HIST_USERID_P nologging;
alter index IDX_OOH_CHARGEUSERID_P nologging;
alter index IDX_OOH_ORDERNUMBER_P nologging;
alter index IDX_OOH_ACTIONDATE_P nologging;
alter table mm_ppms.ONDEMAND_PUSH_INT nologging;
alter table mm_mmpms.ONDEMAND_ORDER_HIST_INT nologging;
alter table mm_mmps.CDR_INT nologging;
6.由脚本call.sh调用start_redef_table.sql开始转换分区。
由于分区转换的时间可能比较长，所以最好由shell脚本调用sql脚本，通过nohup的方式后台执行。在分区转换的过程中，要密切监视undo表空间的使用情况。
start_redef_table.sql脚本的内容如下：
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
查看过程PROCEDURE START_REDEF_TABLE的参数情况如下，我们只需要指定参数UNAME，ORIG_TABLE，INT_TABLE即可。
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
一个简单的call.sh的脚本如下：
#!/bin/ksh
# $Header: call.sh 6-aug-2010.11:56 zhao,lehuan $
# call.sh
# Copyright (c) Aspire. All rights reserved.
# NAME
# call.sh -
# DESCRIPTION
#
# MODIFIED (MM/DD/YY)
#set -x
if [ $# -lt 1 ]
then
echo "Usage:$0
exit 1
fi
SCRIPT_NAME=$1
DB_ID=mmcore
. ~oracle/.profile
##############################################
#Set work dir
WORKPATH=/oracle/utils/partition
##############################################
#Set DATE format
NOWDATE=`date '+%Y%m%d'`
NOWTIME=`date '+%Y-%m-%d-%H:%M'`
################################################
#Set mail recipients and subject
MAILLIST="zhaolehuan@aspire-tech.com"
MAILSUBJECT="call_script_$SCRIPT_NAME_$NOWTIME"
###################################
##############################################
#Load passwd file
#/oracle/utils/orapwdtab format:
#$DB_ID:$USERNAME:$PASSWD
PASSWDFILE=${WORKPATH}/.orapwdtab
if [ ! -f ${PASSWDFILE} ]
then
cp /oracle/utils/orapwdtab ${WORKPATH}/.orapwdtab
chmod 600 ${WORKPATH}/.orapwdtab
fi
if [ ! -f ${PASSWDFILE} ]
then
echo "Can not find password file ${PASSWDFILE}"
exit
fi
USERNAME=mm_ppms
PASSWD=`grep -v "^[#]" ${PASSWDFILE}|grep -i ${DB_ID}|grep -i ${USERNAME}| cut -d: -f3`
cd ${WORKPATH}
###########################################################################
sqlplus -s ${USERNAME}/${PASSWD}@${DB_ID} <
set echo off
set feedback on
set echo on
set trimspool on
set heading on
set timing on
set time on
spool ${WORKPATH}/${SCRIPT_NAME}.file
/* add sql script*/
@ SCRIPT_NAME
spool off
exit;
eof
cat ${WORKPATH}/${SCRIPT_NAME}.file
mailx -m -s ${MAILSUBJECT} -r oracle@`hostname`.com $MAILLIST <${SCRIPT_NAME}.file
exit
7.通过调用脚本sync_interim_table.sql来进行同步数据
如果开始转换和结束转换之间的时间比较长，要么，在这期间可以通过调用上述脚本来进行，SYNC_INTERIM_TABLE这个程序的参数调用与start的调用是一样的。
sync_interim_table.sql脚本的内容如下：
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
8. 由脚本call.sh调用finish_redef_table.sql结束分区转换。
同步完成后，便可以结束分区转换，一般来说，这个过程很快就结束，转换结束后，原普通表的表名事实上对应新创建的分区表，分区表的表名对应原普通表。
finish_redef_table.sql脚本的内容如下，调用参数参见以上说明。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','CDR', 'CDR_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_ORDER_HIST', 'ONDEMAND_ORDER_HIST_INT');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MM_PPMS','ONDEMAND_PUSH', 'ONDEMAND_PUSH_INT');
9.对转换完毕的分区表进行表分析，收集统计信息
由于是生产系统，可以采用较低的采样比迅速完成分析，然后再在系统非高峰期的时候采用恰当的采样比对上述表进行一次表分析。
分析脚本内容如下：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'CDR',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_ORDER_HIST',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MM_PPMS',TABNAME=>'ONDEMAND_PUSH',estimate_percent =>5,CASCADE=>TRUE,DEGREE=>8);
10. 将三张表的表及索引改为logging的状态
logging;
alter index PK_ONDEMAND_PUSH_P logging;
alter index ONDEMAND_ORDER_HIST_INT logging;
alter index IDX_ON_ORDER_HIST_USERID_P logging;
alter index IDX_OOH_CHARGEUSERID_P logging;
alter index IDX_OOH_ORDERNUMBER_P logging;
alter index IDX_OOH_ACTIONDATE_P logging;
alter table ONDEMAND_PUSH logging;
alter table ONDEMAND_ORDER_HIST logging;
alter table CDR logging;
11.检查数据库无效对象，并进行编译，参考如下的脚本进行编译：
sqlplus /nolog <
conn / as sysdba
---recompile all
--@$ORACLE_HOME/rdbms/admin/utlrp.sql;
---compile mocsacct
EXEC DBMS_UTILITY.COMPILE_SCHEMA('MM_PPMS',TRUE);
exit;
EOF
12.后续临时表清理
数据验证无误后，将三张普通表（ONDEMAND_ORDER_HIST_INT，CDR_INT，ONDEMAND_PUSH_INT
）从数据库中删除。
以上的操作过程中，开始转换分区三张表一共花了2个小时左右，这三张表的数据量总数在20G左右，为了减少对undo表空间的争用，三张表依次进行转换，同步的过程很快，在10分钟内就同步完成，结束转换的操作不到两分钟就完成了。而整个操作对生产系统基本无影响，从业务上感觉不到整个转换的过程。
附：CDR表新加列的操作
在CDR表的转换之前，由于分区键createtime原表上是没有的，需要创建新的一列，并将cdrtime列的内容转换成日期格式后更新到新的列中，由于cdr表的数据量在3000多万条，所以只能将此更新分割为较小的事务进行操作，该更新通过编写一个procedure来完成，整个操作步骤如下所示：
整个操作还是由call.sh调用add_newcolumn_on_cdr.sql来完成，add_newcolumn_on_cdr.sql的内容如下：
alter table mm_ppms.cdr add(createtime date);
alter table mm_ppms.cdr modify(createtime date default sysdate);
exec zlh_update_cdr(100000);
ALTER TABLE MM_PPMS.CDR MODIFY CREATETIME NOT NULL;
上述脚本中的过程exec zlh_update_cdr的功能为更新cdrtime的内容到createtime列，参数为提交的频率，如上即为10W行记录一次提交。代码如下：
create or replace procedure mm_ppms.zlh_update_cdr( interval number)
is
v_maxvalue number;
v_minvalue number;
v_range number;
v_interval number;
v_date varchar2(30);
v_looptime number;
v_l_cdrseq number;
v_h_cdrseq number;
errid number;
/*
create table mm_ppms.zlh_cdr_log(
label varchar2(30),
description varchar2(500),
logtime date
)
*/
cursor cur_cdr is select min(TO_NUMBER(CDRSEQ)) minv,
max(TO_NUMBER(CDRSEQ)) maxv,
max(TO_NUMBER(CDRSEQ))- min(TO_NUMBER(CDRSEQ)) rangev
from mm_ppms.cdr;
begin
/*
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
*/
select to_char(sysdate,'yyyymmddhh24miss') into v_date from dual;
v_interval:= interval;
for rec in cur_cdr LOOP
begin
v_minvalue:=rec.minv;
v_maxvalue:=rec.maxv;
v_range:=rec.rangev;
end;
end loop;
v_looptime:=trunc(v_range/interval,0)+1;
/*begin update table cdr */
INSERT INTO zlh_cdr_log
VALUES(v_date,'begin update cdr !',sysdate);
v_l_cdrseq:=v_minvalue;
v_h_cdrseq:=v_minvalue+v_interval;
for i in 1 .. v_looptime loop
update mm_ppms.cdr set createtime=to_date(cdrtime,'yyyymmddhh24miss')
where TO_NUMBER(CDRSEQ) between v_l_cdrseq and v_h_cdrseq;
commit;
insert into zlh_cdr_log values(v_date,'update '||v_interval||' rows of table cdr:looptime:'||i||',current cdrseq range is:'||v_h_cdrseq||',mixvalue:'||v_minvalue||'. maxvalue:'||v_maxvalue||'.',sysdate);
commit;
v_l_cdrseq:=v_h_cdrseq+1;
if v_l_cdrseq+v_interval
v_h_cdrseq:=v_l_cdrseq+v_interval;
else
v_h_cdrseq:=v_maxvalue;
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
errid:=SQLCODE;
INSERT INTO zlh_cdr_log
VALUES(v_date,'error:ERRID ='||TO_CHAR(ERRID),sysdate);
commit;
INSERT INTO zlh_cdr_log
VALUES(v_date,'update cdr completed!',sysdate);
commit;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21601207/viewspace-682823/ ，如需转载，请注明出处，否则将追究法律责任。
