from:http://blog.csdn.net/dbanote/article/details/9935185
对于一个7*24的在线生产系统来说,修改表定义(DDL)时一件非常痛苦的时,因为如果直接使用alter语句,将会在表上安放一个排他锁,也就是说在这期间所有的DML和select都无法操作,如果是一个大表,alter的时间将很长,在这期间应用会受到很大的影响。
幸好,从9i开始,Oracle提供了在线表重定义功能,在修改表定义的同时几乎不影响DML和select语句,因为排他锁只会在表上出现很短时间。但是在线表重定义需要额外的空间(大致等于原表空间大小)。
在线表重定义具有如下功能:
- 修改表的存储参数;
- 可以将表转移到其他表空间;
- 增加并行查询选项;
- 增加或删除分区;
- 重建表以减少碎片;
- 将堆表改为索引组织表或相反的操作;
- 增加或删除一个列。
Oracle提供DBMS_REDEFINITION包来进行在线表重定义,下面以把一个普通表重定义成分区表为例,说明在线表重定义的主要步骤:
1. 执行CAN_REDEF_TABLE,验证目标表是否可以在线重定义:
表的原始定义如下:
- 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的方法(这也是最常用的方法),验证如下:
- 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. 创建一个空的中间表,这个中间表就是你期望重定义后的表结构:
中间表(即新表)定义如下:
- 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) 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),
- OPERATIONDATE CHAR(8) DEFAULT to_char(SYSDATE,'yyyymmdd')
- ) 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;
新表(中间表)和原表相比,做了如下修改:增加分区列(OPERATIONDATE,数据类型为CHAR(8),默认值为sysdate)。
这里需要注意的是:不需要创建约束、索引等,只需要表定义即可,因为像约束、索引等这样的依赖对象会再第5步中自动创建和拷贝。
3. 对于大表,最好设置合理的并行度提高性能:
- alter session force parallel dml parallel 4;
- alter session force parallel query parallel 4;
4. 执行START_REDEF_TABLE,从原表拷贝数据至中间表:
- BEGIN
- DBMS_REDEFINITION.START_REDEF_TABLE(
- uname => 'p95169',
- orig_table => 'shift_case',
- int_table => 'shift_case_interim',
- col_mapping => 'SCID SCID,
- ESTID ESTID,
- CLINICTYPEUUID CLINICTYPEUUID,
- 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,
- NVL(CASETYPE,0) CASETYPE,
- HOSPRESOURCEID HOSPRESOURCEID,
- HOSPTIMESECTION HOSPTIMESECTION,
- HOSPTREATMENTTIME HOSPTREATMENTTIME,
- COMMENTS COMMENTS,
- SHIFTDATE OPERATIONDATE',
- options_flag => dbms_redefinition.cons_use_pk);
- END;
- /
这里着重要讲一下col_mapping的设置:
[expression] column_name
其中column_name是新表的列名,[experssions]表达式可以从原表的列、常量、操作符、函数调用等,比如上面的(NVL(CASETYPE,0) CASETYPE)就表示如果原表列CASETYPE为NULL,则新表的CASETYPE为0,否则就把原表该列的值赋予新表。
这一存储过程把原表的数据拷贝至新表,并通过物化视图不断刷新,保持原表和中间表的实时同步。
5. 拷贝依赖对象(索引,触发器等)和统计信息:
- 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查询上一步的错误信息:
- 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. (可选)同步中间表
- BEGIN
- DBMS_REDEFINITION.SYNC_INTERIM_TABLE('p95169', 'shift_case','shift_case_interim');
- END;
- /
8. 执行FINISH_REDEF_TABLE结束表重定义:
- BEGIN
- DBMS_REDEFINITION.FINISH_REDEF_TABLE('p95169', 'shift_case','shift_case_interim');
- END;
- /
只有在这个过程中,原表上有排他锁,但时间很短,对应用几乎没有影响,执行成功后,就用中间表替代原表。
这个过程实质是把原表和中间表(包括它们各自依赖的对象,如索引、约束等)对换,是数据字典级别的,所以非常快。
9. Drop中间表
因为上一步原表和中间表进行了对换,所以这步中的中间表就是原表,只是名称不一样而已。现在已经可以drop掉中间表,释放空间。如果为了安全起见,可以暂时先不drop,等过段时间再drop也可以。
注意:
注释(Comment)没有被复制到新表,记得重建Comment