表的在线重定义方法介绍及使用DBMS_REDEFINITION包案例.docx

表的在线重定义方法介绍及使用DBMS_REDEFINITION包案例随着技术的发达，业务的要求也越来越高，一般的数据库都会承载7X24不间隔业务运行。我们有时候需要对表做一些结构上的调整，比如增加/删除一个字段，修改一个字段等DDL操作，而这种操作对于7X24运行的业务的话，如果采用方法不对，可能对数据库就是一个灾难。以下列出解决此问题的方法并详细介绍DBMS_REDEFINITION包的功能。1、 直接使用alter table的方法。2、 创建临时表，删除原表，改表名，创建索引/约束/触发器。3、 使用DBMS_REDEFINITION包进行。第1种方法最明显的优点就是操作简单，一条命令就搞定，节约空间，不需要额外存储。但是对于小表来说可行，对于大业务的大表来说，有可能就是一场灾难，因为在使用这个方法的时候会对整个表加排他锁，会导致所有对该表的操作均处于等待，而大表做此操作往往需要很长的时间，长时间对表加排他锁，其他操作的等待会一直累积，造成的后果小则业务不响应，大则系统hang住，所以此法需要慎用，仅用于小业务的小表上。第2种方法解决了第1种方法的绝大多数问题，表数据仅在删除原表和改表名之间不可用，而这个过程非常短，所以其解决了对表加排他锁的问题。但是其操作相对比较复杂，需要提前做好每步的内容；需要即时将约束和触发器建上，不然可能影响业务的正确性和一致性；在创建索引完成之前，整个表的访问均走全表扫描，可能会导致大量的IO，从而影响业务执行效率；需要至少2倍表大小的存储空间，在创建临时表时，原表也同时存在。这种方法可以用于业务量不是很大的表上，不过需要提前准备好创建索引，约束，触发器的脚本！第3种方法是ORACLE自带的一种工具包，专门用于进行表的重构。该种方法中和了前2种方法的优点，能最小化对系统的影响，仅在最后完成重定义一步会对表加排他锁，而这一步耗时也相对较小。该方法需要至少2倍表和索引大小的存储空间，相对第2步增加了索引的存储空间。并且需要DBA给予用户执行DBMS_REDEFINITION包的权限。在大业务的大表上请使用此种方法。下面详细介绍一下如何使用DBMS_REDEFINITION包进行表的重构。大体步骤为：1）准备工作包括表结构，表记录数，表存储大小，表所在的表空间大小，表上的索引，表上的约束，表上的触发器，触发器中表的数据，失效对象2）在线重定义2.1）检查是否可以在线重定义2.2）创建中间表2.3）开始重定义2.4）拷贝依赖对象2.5）完成重定义3）核查工作包括表结构，表记录数，表存储大小，表所在表空间大小，表上的索引，表上的约束，表上的触发器，触发器中表的数据，失效对象下面展示一个真实的案例：1、 准备工作a) 查看表的基本信息desc WMCADMIN.COLORRINGINFO_RELATIONselect count(1) from WMCADMIN.COLORRINGINFO_RELATION;select TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION,WMCADMIN) FROM DUAL;b) 查看表以及索引情况select bytes/1024/1024,tablespace_name from dba_segments where owner=WMCADMIN AND SEGMENT_NAME=COLORRINGINFO_RELATION;select idx.owner,index_name,index_type,IDX.tablespace_name,bytes/1024/1024,status from dba_indexes idx, dba_segments seg where idx.owner=seg.owner and idx.index_name=seg.segment_name and idx.owner=WMCADMIN AND IDX.TABLE_NAME=COLORRINGINFO_RELATION;总共需要空间：表空间还剩select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=BUSIDATA0;表空间剩余足够。c) 查看约束和触发器情况select constraint_name,constraint_type,SEARCH_CONDITION,status from dba_constraints where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;select trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;select text from dba_source where owner=WMCADMIN and name = TRI_COLORRINGINFO_RELATIONselect count(1) from wmcadmin.colorringinfo_relation_add;select count(1) from wmcadmin.colorringinfo_relation_add2;d) 查看失效对象情况select count(1) from dba_objects where status=INVALID;select owner,object_name from dba_objects where status=INVALID;SELECT * FROM DBA_REDEFINITION_OBJECTS;SELECT * FROM DBA_REDEFINITION_ERRORS;2、在线重定义2.1、检查是否可以进行在线重定义EXEC DBMS_REDEFINITION.can_redef_table(WMCADMIN, COLORRINGINFO_RELATION);2.2、创建中间表DESC WMCADMIN.COLORRINGINFO_RELATION_TMPcreate table wmcadmin.COLORRINGINFO_RELATION_TMP AS SELECT * FROM WMCADMIN.COLORRINGINFO_RELATION WHERE 1=2;select TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION_TMP,WMCADMIN) FROM DUALalter table wmcadmin.COLORRINGINFO_RELATION_TMP add (marketing_bal varchar2(1) default 0 not null);2.3、开始重定义ALTER SESSION FORCE PARALLEL DML PARALLEL 4;ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;EXEC DBMS_REDEFINITION.start_redef_table(WMCADMIN,COLORRINGINFO_RELATION,COLORRINGINFO_RELATION_TMP);select MARKETING_BAL from WMCADMIN.COLORRINGINFO_RELATION_TMP where rownum=1;select count(1) from WMCADMIN.COLORRINGINFO_RELATION_TMP;2.4、拷贝依赖对象DECLARE RETVAL NUMBER (5); BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (WMCADMIN, COLORRINGINFO_RELATION, COLORRINGINFO_RELATION_TMP,IGNORE_ERRORS=TRUE,NUM_ERRORS= RETVAL); DBMS_OUTPUT.PUT_LINE (RETVAL); END; /SELECT * FROM DBA_REDEFINITION_OBJECTSSELECT * FROM DBA_REDEFINITION_ERRORS2.5完成重定义EXEC DBMS_REDEFINITION.finish_redef_table(WMCADMIN, COLORRINGINFO_RELATION, COLORRINGINFO_RELATION_TMP);2、 核查工作a) 检查表结构desc WMCADMIN.COLORRINGINFO_RELATIONselect TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION,WMCADMIN) FROM DUAL;select count(1) from WMCADMIN.COLORRINGINFO_RELATIONselect sum(bytes)/1024/1024 from dba_free_space where tablespace_name=BUSIDATA0;b) 检查索引select idx.owner,index_name,index_type,IDX.tablespace_name,bytes/1024/1024,status from dba_indexes idx, dba_segments seg where idx.owner=seg.owner and idx.index_name=seg.segment_name and idx.owner=WMCADMIN AND IDX.TABLE_NAME=COLORRINGINFO_RELATIONc) 检查约束和触发器select constraint_name,constraint_type,SEARCH_CONDITION,status from dba_constraints where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATIONselect trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;d) 检查失效对象select count(1) from dba_objects where status=INVALID;select owner,object_name from dba_objects where status=INVALID;select table_name,trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND trigger_name=TRI_COLORRINGINFO_RELATION;alter trigger WMCADMIN.TRI_COLORRINGINFO_RELATION compile;select owner,object_name,object_type from dba_objects where status=INVALID;select table_name,trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND trigger_name=