[Oracle]在线表重定义 - 普通表到分区表
[Oracle]在线表重定义 - 普通表到分区表
对于一个7*24的在线生产系统来说，修改表定义（DDL）时一件非常痛苦的时，因为如果直接使用alter语句，将会在表上安放一个排他锁，也就是说在这期间所有的DML和select都无法操作，如果是一个大表，alter的时间将很长，在这期间应用会受到很大的影响。
幸好，从9i开始，Oracle提供了在线表重定义功能，在修改表定义的同时几乎不影响DML和select语句，因为排他锁只会在表上出现很短时间。但是在线表重定义需要额外的空间（大致等于原表空间大小）。
在线表重定义具有如下功能：
修改表的存储参数；
可以将表转移到其他表空间；
增加并行查询选项；
增加或删除分区；
重建表以减少碎片；
将堆表改为索引组织表或相反的操作；
增加或删除一个列。
Oracle提供DBMS_REDEFINITION包来进行在线表重定义，下面以把一个普通表重定义成分区表为例，说明在线表重定义的主要步骤：
1. 执行CAN_REDEF_TABLE，验证目标表是否可以在线重定义：
表的原始定义如下：
[sql]
CREATE TABLE P95169.SHIFT_CASE
( SCID VARCHAR2(40) NOT NULL ENABLE,
ESTID VARCHAR2(40),
CLINICTYPEUUID VARCHAR2(40) NOT NULL ENABLE,
EXPERTID VARCHAR2(40) NOT NULL ENABLE,
CLINICADDRESS VARCHAR2(100),
FEE NUMBER(10,0),
UPDEPART VARCHAR2(50),
GETTIME VARCHAR2(50),
GETADDRESS VARCHAR2(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,
CONSTRAINT CK_SHIFTCASE_ORDERINGCOUNT CHECK (orderingcount>=0) ENABLE,
CONSTRAINT CK_SHIFTCASE_SHARERCCOUNT CHECK (sharerccount>=0) ENABLE,
CONSTRAINT PK_SHIFTCASE PRIMARY KEY (SCID) USING INDEX TABLESPACE DATA ENABLE
) TABLESPACE DATA;
有两种重定义的方法：by key和by rowid，因为本例源表有PK，所以采用by key的方法（这也是最常用的方法），验证如下：
[sql]
SYS@TEST16>exec DBMS_REDEFINITION.CAN_REDEF_TABLE('p95169','shift_case',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
如果你采用by rowid，就把CONS_USE_PK替换成CONS_USE_ROWID即可。
2. 创建一个空的中间表，这个中间表就是你期望重定义后的表结构：
本例需要增加一列OPERATIONDATE，数据类型为CHAR(8)，该列的值等于SHIFTDATE；接着以该列为分区键，以1个月对该表做范围分区；最后，还修改了主键索引的表空间，中间表的DDL如下：
[sql]
CREATE TABLE P95169.SHIFT_CASE_INTERIM
( SCID VARCHAR2(40) NOT NULL ENABLE,
ESTID VARCHAR2(40),
CLINICTYPEUUID VARCHAR2(40) NOT NULL ENABLE,
EXPERTID VARCHAR2(40) NOT NULL ENABLE,
CLINICADDRESS VARCHAR2(100),
FEE NUMBER(10,0),
UPDEPART VARCHAR2(50),
GETTIME VARCHAR2(50),
GETADDRESS VARCHAR2(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),
