[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),
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,COL03 COL03,COL04 COL04,COL05 COL05,
CASETYPE CASETYPE,HOSPRESOURCEID HOSPRESOURCEID,HOSPTIMESECTION HOSPTIMESECTION,
HOSPTREATMENTTIME HOSPTREATMENTTIME,COMMENTS COMMENTS, HOSPITALUUID HOSPITALUUID,
OPEN_TIME OPEN_TIME,SCID SCID,ESTID ESTID,CLINICTYPEUUID CLINICTYPEUUID,
SHIFTDATE OPERATIONDATE',
dbms_redefinition.cons_use_pk);
END;
这步开始拷贝数据，因此需要一些时间，在这过程中，依然可以在原表上做select查询和DML操作。
5. 拷贝依赖对象（索引，触发器等）和统计信息：
[sql]
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('p95169', 'shift_case','shift_case_interim',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
注意：上面我们把ignore_errors设为true，表示即时有错误发生，也不会导致改存储过程失败，这时你一定要记得在下一步查看是否有错误。
6. 从视图DBA_REDEFINITION_ERRORS查询上一步的错误信息：
[sql]
SYS@TEST16>select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
PK_SHIFTCASE_2 SHIFT_CASE CREATE UNIQUE INDEX "P95169"."TMP$$_PK_SHIFTCASE_20" ON "P95169"."SHIFT_CASE_INT
ERIM" ("SCID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FL
ASH_CACHE DEFAULT)
TABLESPACE "DATA"
SYS_C009009 SHIFT_CASE ALTER TABLE "P95169"."SHIFT_CASE_INTERIM" MODIFY ("CASETYPE" CONSTRAINT "TMP$$_S
YS_C0090090" NOT NULL ENABLE NOVALIDATE)
类似以上的错误信息是因为中间表已经有主键和约束，所以导致失败，这些错误可以忽略。
7. （可选）同步中间表
[sql]
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('p95169', 'shift_case','shift_case_interim');
END;
/
8. 执行FINISH_REDEF_TABLE结束表重定义：
[sql]
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('p95169', 'shift_case','shift_case_interim');
END;
/
在这过程中，原表上有排他锁，但时间很短，对应用几乎没有影响，执行成功后，原表定义已成功改变。
9. Drop中间表
drop table shift_case_interim;
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。