ITPub博客

使用DBMS_REDEFINITION包执行在线重定义表(ONLINE TABLE REDEFINITION)

原创 作者:尛样儿 时间:2015-08-07 21:57:37 0 删除 编辑
<span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;这篇文章参考官方文档,简单讨论一下使用DBMS_REDEFINITION包执行在线重定义表。在线重定义表技术是从9i开始就出现的技术,该技术可以有效的减少对表逻辑结构和物理结构的调整给业务系统带来的影响,在对某个表做在线重定义操作的过程并不影响对原表的查询和DML操作,只是在结束在线重定义操作的时候会对原表有短暂的锁定。在线重定义和传统方法相比,增强了数据库的可用性。对表的在线重定义由于要产生中间表,所以要求剩余至少和原表空间相同,甚至更大。<span style="font-family:'Courier New';font-size:16px;line-height:24px;white-space:normal;">在线重定义功能实际是通过物化视图来实现的。</span>我们可以使用OEM或者DBMS_REDEFINITION包完成在线重定义操作。<br /> <br /> 在线表重定义可以达到以下的目的:<br /> 1).修改表或集群的存储参数。<br /> 2).移动表或集群到不同的表空间。<br /> 3).增加,修改或者DROP表或集群的一个或多个字段。<br /> 4).改变分区结构。<br /> 5).改变单一表分区的物理属性,包括一定它到相同用户的不同表空间下。<br /> 6).改变物化视图日志的物理属性或者Oracle Streams高级队列队列表。<br /> 7).添加并行查询支持。<br /> 8).重建表或者集群减少碎片,在很多情况下,在线段shrink是减少碎片更容易的方法。<br /> 9).将普通表转换为IOT,或者将IOT转换为普通表。<br /> 10).转换一个相关表到表的对象字段,或者反向转换。<br /> 11).转换一个对象表到相关的表或者表的对象字段,或者反向转换。</span><span style="font-family:'Courier New';font-size:16px;line-height:1.5;"></span><span style="font-family:'Courier New';font-size:16px;line-height:1.5;"></span><span style="font-size:16px;font-family:'Courier New';"><br /> <br /> 普通用户要想使用DBMS_REDEFINITION包,需要拥有以下权限:</span><br /> <span style="font-size:16px;font-family:'Courier New';">EXECUTE_CATALOG_ROLE角色。</span><br /> <span style="font-size:16px;font-family:'Courier New';">CREATE ANY TABLE.</span><br /> <span style="font-size:16px;font-family:'Courier New';">ALTER ANY TABLE.</span><br /> <span style="font-size:16px;font-family:'Courier New';">DROP ANY TABLE.</span><br /> <span style="font-size:16px;font-family:'Courier New';">LOCK ANY TALBE.</span><br /> <span style="font-size:16px;font-family:'Courier New';">SELECT ANY TABLE.</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">执行COPY_TABLE_DEPENDENTS过程还需要以下权限:</span><br /> <span style="font-size:16px;font-family:'Courier New';">CREATE ANY TRIGGER.</span><br /> <span style="font-size:16px;font-family:'Courier New';">CREATE ANY INDEX.</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>下面讨论一下使用DBMS_REDEFINITION包执行在线重定义表的步骤:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"><strong>1.选择重定义方法:by key或者by rowid</strong></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;<strong>By key</strong>,选择主键或者所有字段有NOT NULL约束的唯一键用于在线重定义操作。使用这种方法,在线重定义之前和之后表应该有相同的主键字段,这是在线重定义操作默</span><span style="font-size:16px;font-family:'Courier New';">认和优先选择的方法。<br /> </span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;<strong>By rowid</strong>,如果没有主键可以使用这种方法,使用这种方法,隐藏字段M_ROW$$被添加到重定义后的表中。推荐在重定义表之后将这个字段DROP掉,或者将其标记为</span><span style="font-size:16px;font-family:'Courier New';">UNUSED。如果COMPATIBLE初始化参数设置为10.2.0或者更高,最终在线重定义的语法将自动设置这个字段为UNUSED。可以使用ALTER TABLE ... DROP UNUSED COLUMNS</span><span style="font-size:16px;font-family:'Courier New';">语句DROP掉它。不能在索引组织表(IOT)上使用该方法。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>2.验证表是否能被在线重定义。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;通过调用CAN_REDEF_TABLE过程验证表是否能被在线重定义。如果表不能作为在线重定义表的候选表,那么这个过程提示一个错误,并且会表明为什么该表不能在线重定</span><span style="font-size:16px;font-family:'Courier New';">义。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>3.创建一个空的中间表。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;按照期望的逻辑和物理属性在在线重定义的表相同的用户下创建一个空的中间表,如果要DROP字段,那么在中间表中不要包含它们,如果是新增加字段,那么在中间表中</span><span style="font-size:16px;font-family:'Courier New';">添加这些字段,如果是修改字段,那么在中间表按照想要的结果修改它们。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">创建中间表不是必须按照重定义的表创建所有的索引,约束,授权,和触发器。因为这些对象可以在下面的拷贝依赖对象的步骤中可以被自动定义。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>4.启动行迁移功能。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;如果采用rowid的方法重定义一个分区表,启用中间表的行迁移功能。</span><br /> <span style="font-size:16px;font-family:'Courier New';">ALTER TABLE ... ENABLE ROW MOVEMENT.</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>5.启用会话并行功能。</strong><br /> </span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;这是可选的步骤,如果重定义一张大表,想通过并行方式提升下面步骤的性能,执行以下的语句:</span><br /> <span style="font-size:16px;font-family:'Courier New';">ALTER SESSION FORCE PARALLEL DML PARALLEL &lt;degree-of-parallelism&gt;;</span><br /> <span style="font-size:16px;font-family:'Courier New';">ALTER SESSION FORCE PARALLEL QUERY PARALLEL &lt;degree-of-parallelism&gt;;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>6.开始在线重定义过程。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> 提供以下的信息调用START_REDEF_TABLE过程开始重定义过程:</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;被在线重定义的用户名和表名。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;中间表名称。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;字段映射字符串,映射被重定义表的字段到中间表的字段。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;重定义方法。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;DBMS_REDEFINITION.CONS_USE_PK代表by key方法,这也是默认的方法。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;DBMS_REDEFINITION.CONS_USE_ROWID代表by rowid方法。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;在排序行中使用的字段。</span><br /> <span style="font-size:16px;font-family:'Courier New';">&gt;如果重定义的分区表只有一个分区,分区名。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;因为这步会拷贝数据,所以可能会持续一段时间,表在被重新定义的整个过程中,查询和DML操作仍然是可执行的。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>注意:</strong>如果START_REDEF_TABLE以为某种原因失败,必须调用ABORT_REDEF_TABLE过程,否则接下来重新定义表将失败。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>7.拷贝依赖的对象。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;重被重定义的表中拷贝依赖的对象(例如,触发器,索引,物化视图日志,授权和约束)和统计信息到中间表,使用以下两种方法的其中一种,方法一是首选的方法,因</span><span style="font-size:16px;font-family:'Courier New';">为它是自动的,该方法也会拷贝统计信息到中间表。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&gt;方法一:自动创建依赖对象</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;使用COPY_TABLE_DEPENDENTS过程自动创建中间表的依赖对象,这个过程也叫做注册依赖对象。在重定义过程完成后,依赖对象的名字将和原有名字保持一致。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&gt;方法二:手动创建依赖对象</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;可以手动创建中间表的依赖对象,并且注册它们。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>8.完成在线重定义过程。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;执行FINISH_REDEF_TABLE过程完成重定义表过程。在这个过程中,原始表将以排他模式被锁定非常短的时间,时间长短依赖于原始表中独立的(与中间表的数据差)数据</span><span style="font-size:16px;font-family:'Courier New';">量,然而,FINISH_REDEF_TABLE在完成重定义之前将等待所有的DML操作提交。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>9.处理M_ROW$$字段。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;如果使用rowid在线重定义,并且COMPATIBLE初始化参数设置为10.1.0或者更低,DROP或者设置重定义后的表中出现的M_ROW$$隐藏字段为UNUSED。</span><br /> <span style="font-size:16px;font-family:'Courier New';">ALTER TABLE table_name set UNUSED(M_ROW$$);</span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;如果COMPATIBLE初始化参数为10.2.0或者更高,当重定义完成该隐藏字段将自动被设置为UNUSED。可以使用ALTER TABLE ... DROP UNUSED COLUMNS语句DROP该字段。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>10.DROP中间表。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"><br /> 等待任何针对中间表长时间运行的查询完成,然后DROP中间表。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">如果对活动的中间表查询执行DROP操作,可能收到ORA-08103的错误("object no longer exists")。</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>下面通过例子说明在线重定义表的过程:</strong></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>1.验证表在线重定义的条件,使用主键方法进行在线重定义验证:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">BEGIN</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; DBMS_REDEFINITION.CONS_USE_PK);</span><br /> <span style="font-size:16px;font-family:'Courier New';">END;</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>2.创建中间表hr.int_admin_emp:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">CREATE TABLE hr.int_admin_emp</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; (empno &nbsp; &nbsp; &nbsp;NUMBER(5) PRIMARY KEY,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ename &nbsp; &nbsp; &nbsp;VARCHAR2(15) NOT NULL,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;job &nbsp; &nbsp; &nbsp; &nbsp;VARCHAR2(10),</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;mgr &nbsp; &nbsp; &nbsp; &nbsp;NUMBER(5),</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;hiredate &nbsp; DATE DEFAULT (sysdate),</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sal &nbsp; &nbsp; &nbsp; &nbsp;NUMBER(7,2),</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;deptno &nbsp; &nbsp; NUMBER(3) NOT NULL,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bonus &nbsp; &nbsp; &nbsp;NUMBER (7,2) DEFAULT(1000))</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp;PARTITION BY RANGE(empno)</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp;(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>3.开始重定义过程。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">BEGIN</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp;'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; dbms_redefinition.cons_use_pk);</span><br /> <span style="font-size:16px;font-family:'Courier New';">END;</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>4.拷贝依赖对象(自动在hr.int_admin_emp表上创建触发器,索引,物化视图日志,授权和约束)。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">DECLARE</span><br /> <span style="font-size:16px;font-family:'Courier New';">num_errors PLS_INTEGER;</span><br /> <span style="font-size:16px;font-family:'Courier New';">BEGIN</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);</span><br /> <span style="font-size:16px;font-family:'Courier New';">END;</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>注意:</strong>在这儿ignore_errors参数设置为TRUE,根据实际情况,部分错误可直接忽略。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>5.查询DBA_REDEFINITION_ERRORS试图查询错误:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select object_name, base_table_name, ddl_txt from</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DBA_REDEFINITION_ERRORS;</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;</span><br /> <span style="font-size:16px;font-family:'Courier New';">OBJECT_NAME &nbsp; BASE_TABLE_NAME &nbsp;DDL_TXT</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------- ---------------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">SYS_C005836 &nbsp; ADMIN_EMP &nbsp; &nbsp; &nbsp; &nbsp;CREATE UNIQUE INDEX "HR"."TMP$</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$_SYS_C0058360" ON "HR"."INT_A</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DMIN_EMP" ("EMPNO")</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;</span><br /> <span style="font-size:16px;font-family:'Courier New';">SYS_C005836 &nbsp; ADMIN_EMP &nbsp; &nbsp; &nbsp; &nbsp;ALTER TABLE "HR"."INT_ADMIN_EM</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;P" ADD CONSTRAINT "TMP$$_SYS_C</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0058360" PRIMARY KEY</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>6.(可选),同步中间表。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">BEGIN&nbsp;</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');</span><br /> <span style="font-size:16px;font-family:'Courier New';">END;</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>7.完成重定义。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">BEGIN</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');</span><br /> <span style="font-size:16px;font-family:'Courier New';">END;</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;hr.admin_emp将被以排他模式锁定很短的时间,直到这步结束,之后hr.admin_emp被重定义完成,hr.admin_emp将有hr.int_admin_emp表的所有属性。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>8.等待任何针对中间表长时间运行的查询结束,然后DROP中间表。</strong></span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;另外,在线重定义表可以通过OEM非常容易的完成在TABLE页面,选择要重定义表的字段,从Actions List中选择Reorganize。<br /> &nbsp; &nbsp;使用传统的表重定义方法可能遇到一些问题,可以参考文章《Oracle Database将普通表转换为分区表遇到的问题》:<a href="http://blog.itpub.net/23135684/viewspace-1753024/" target="_blank">http://blog.itpub.net/23135684/viewspace-1753024/<br /> <br /> </a>&nbsp; &nbsp; 《使用DBMS_REDEFINITION在线重定义表普通表为分区表》:<a href="http://blog.itpub.net/23135684/viewspace-661756/" target="_blank">http://blog.itpub.net/23135684/viewspace-661756/</a></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;本文节选自《Oracle Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-02》中的部分内容,更详细的内容和例子请参考这篇文章和对</span><span style="font-size:16px;font-family:'Courier New';">DBMS_REDEFINITION包的解释。</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"><strong>相关文章:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"> &nbsp; &nbsp;《</span><span style="font-size:16px;font-family:'Courier New';">Moving表到新的段或者表空间</span><span style="font-size:16px;font-family:'Courier New';">》</span><span style="font-size:16px;font-family:'Courier New';">:</span><a href="http://blog.itpub.net/23135684/viewspace-1766480/" target="_blank"><span style="font-size:16px;font-family:'Courier New';">http://blog.itpub.net/23135684/viewspace-1766480/</span><span style="font-size:16px;font-family:'Courier New';"></span></a><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">--end--</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br />
请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    461
  • 访问量
    5178484