[Oracle]在线表重定义 - 普通表到分区表(二)
ARCHAR2(100),
ISOPEN NUMBER(1,0) NOT NULL ENABLE,
SEXLIMIT NUMBER(1,0),
AGETOPLIMIT NUMBER(3,0) DEFAULT 150,
AGELOWERLIMIT NUMBER(3,0) DEFAULT 0,
RCLIMIT NUMBER(8,0) NOT NULL ENABLE,
SHIFTDATE CHAR(8) NOT NULL ENABLE,
ISTIMEDIVISION NUMBER(1,0) NOT NULL ENABLE,
ISSELECT NUMBER(1,0) NOT NULL ENABLE,
WEEKDAY NUMBER(1,0) NOT NULL ENABLE,
DAYSECTION NUMBER(1,0) NOT NULL ENABLE,
ORDERINGCOUNT NUMBER(8,0) NOT NULL ENABLE,
SHARERCCOUNT NUMBER(8,0) NOT NULL ENABLE,
CREATETIME CHAR(14) NOT NULL ENABLE,
STATE NUMBER(2,0) NOT NULL ENABLE,
UPDATETIME DATE,
CHANGEREASON VARCHAR2(1000),
STATETIME CHAR(14) NOT NULL ENABLE,
RELATEID VARCHAR2(40),
HOSPDEPTUUID VARCHAR2(40) DEFAULT null NOT NULL ENABLE,
TASKFLAG NUMBER(1,0),
COL01 VARCHAR2(200),
COL02 VARCHAR2(200),
COL03 VARCHAR2(200),
COL04 VARCHAR2(200),
COL05 VARCHAR2(200),
CASETYPE NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
HOSPRESOURCEID VARCHAR2(50),
HOSPTIMESECTION VARCHAR2(50),
HOSPTREATMENTTIME VARCHAR2(50),
COMMENTS VARCHAR2(2000),
HOSPITALUUID VARCHAR2(40),
OPEN_TIME DATE,
OPERATIONDATE CHAR(8) NOT NULL ENABLE,
CONSTRAINT CK_SHIFTCASE_ORDERINGCOUNT CHECK (orderingcount>=0),
CONSTRAINT CK_SHIFTCASE_SHARERCCOUNT CHECK (sharerccount>=0),
CONSTRAINT PK_SHIFTCASE PRIMARY KEY (SCID) USING INDEX TABLESPACE INDX ENABLE
) PARTITION BY RANGE (OPERATIONDATE) (
PARTITION P2012 VALUES LESS THAN ('20130101'),
PARTITION P201301 VALUES LESS THAN ('20130201'),
PARTITION P201302 VALUES LESS THAN ('20130301'),
PARTITION P201303 VALUES LESS THAN ('20130401'),
PARTITION P201304 VALUES LESS THAN ('20130501'),
PARTITION P201305 VALUES LESS THAN ('20130601'),
PARTITION P201306 VALUES LESS THAN ('20130701'),
PARTITION P201307 VALUES LESS THAN ('20130801'),
PARTITION P201308 VALUES LESS THAN ('20130901'),
PARTITION P201309 VALUES LESS THAN ('20131001'),
PARTITION P201310 VALUES LESS THAN ('20131101'),
PARTITION P201311 VALUES LESS THAN ('20131201'),
PARTITION P201312 VALUES LESS THAN ('20140101'),
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
)
TABLESPACE DATA;
3. 对于大表，最好设置合理的并行度提高性能：
[sql]
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
4. 执行START_REDEF_TABLE，真正开始重定义：
[sql]
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('p95169', 'shift_case','shift_case_interim',
'EXPERTID EXPERTID,CLINICADDRESS CLINICADDRESS,FEE FEE,UPDEPART UPDEPART,GETTIME GETTIME,
GETADDRESS GETADDRESS, ISOPEN ISOPEN,SEXLIMIT SEXLIMIT,AGETOPLIMIT AGETOPLIMIT,
AGELOWERLIMIT AGELOWERLIMIT,RCLIMIT RCLIMIT,SHIFTDATE SHIFTDATE,ISTIMEDIVISION ISTIMEDIVISION,
ISSELECT ISSELECT,WEEKDAY WEEKDAY,DAYSECTION DAYSECTION,ORDERINGCOUNT ORDERINGCOUNT,
SHARERCCOUNT SHARERCCOUNT, CREATETIME CREATETIME,STATE STATE,UPDATETIME UPDATETIME,
CHANGEREASON CHANGEREASON,STATETIME STATETIME,RELATEID RELATEID,HOSPDEPTUUID HOSPDEPTUUID,
TASKFLAG TASKFLAG,COL01 COL01,COL02 COL02,CO