Oracle重建表(CTAS+rename)注意事项总结

来源:转载

一、概述前一段时间,有一个DBA朋友在完成重建表(rename)工作后,第二天早上业务无法正常运行,出现数据无法插入的限制和错误,后来分析才发现,错误的原因是使用rename方式重建表以后,其它引用这个表的外键约束指向没有重新定义到这个重建的新表中,从而导致这些表在插入新数据时,违反数据完整性约束,导致数据无法正常插入。影响了业务大概有1个多小时,真是一次血淋淋的教训啊。 使用rename方式重建表是我们日常DBA维护工作中经常使用的一种方法,因为CTAS+rename这种配合方式,非常实用和高效。很多DBA朋友应该也都是用过rename方式重建表,而且重建完成以后也都一切正常,没有引起过问题。但是,我想说的是,使用rename重建表后,具体需要完成哪些扫尾工作你真的清楚吗?? 这篇文章主要就是归纳当我们使用rename方式重建表后,需要进行哪些扫尾工作,如果你还不是很清楚,一定要仔细阅读这篇文章,同时在以后的重建表工作中矫正过来,否则,问题迟早有一天会降临到你的身边! 
二、重建表的方式这里先不谈其它,仅仅说一下重建表的方法。如下:1、为了确保所有表字段、字段类型、长度完全一样,我一般不建议使用CTAS方式来重建表。2、一般我都是使用下面两种方法中的一个,来抽取表的定义select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual;使用PL/SQL developer类似这样的工具,来查看表定义语句3、重新建一张_old类型的表(根据上面的抽取的表定义),然后使用insert /*+ append */ xx select xxx 方式完成数据的转换4、最后使用rename方式倒换这两张表的名字 
三、重建表注意事项 索引重建:这里最关键的是,重建后索引的名字是否必须和以前的一样,如果需要一样,则必须将当前使用的索引名字先rename,否则创建的时候会出现索引名字已经存在的错误,如下: select 'alter index ' || owner || '.' || index_name || ' rename to ' || substr(index_name, 1, 26) || '_old;' from dba_indexes a where a.table_owner = 'DBMON' AND A.table_name = 'DH_T'; 
依赖对象重建:一般可以使用如下方式完成 select 'alter '||decode(type,'PACKAGE BODY','PACKAGE',type)||' '||owner||'.'||name||' compile;' from dba_dependencies a where a.referenced_name = 'DH_T' and a.referenced_owner = 'DBMON'; 注意:1、这里重建的只是直接依赖对象,必须考虑那些间接依赖的对象(例如 view1依赖A表,view2依赖view1),查找方法和上面差不多2、如果这些依赖对象中存在一些私有对象(例如dblink等),我们用DBA用户重新编译是会出现编译错误,对于这种对象,必须以对应对象的所属者才能编译成功。(也可用用10g以后新出现的代理权限来完成这类任务!) 针对PL/SQL代码(包、函数、过程等),是否存在私有对象的查找方法,如下: select * from dba_source a where (a.owner, a.name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON') and a.TEXT like '%@%'; 针对视图中是否存在私有对象的查找方法,如下(由于是long类型,必须得一个一个查看): select * from dba_views a where (a.owner, a.view_name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON' and b.type = 'VIEW') 
权限重建:可以使用如下语句 select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where table_name = upper('&i_table_name') and owner = upper('&i_owner'); 
外键重建:对于外键,现在的业务数据逻辑很多都是在应用层来实现,因此表上的外键可能都非常少,因此,导致很多DBA都忘记需要检查和重建这一部分了,从而导致业务出现问题,本章最开始说的故障案例就是因为没有重建外键而引起,因此我们一定要提高警惕。可以使用如下语句查看,哪些表引用了重建表 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name,--被外键引用的约束名 b.table_name --被外键引用的表名 from dba_constraints a, dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = 'FSPARECEIVEBILLTIME' and b.owner=''; 
物化视图:另外一个非常重要的依赖对象就是物化视图,一般来说,rename表以后,物化视图是不会有问题的,再次刷新时会自动编译,但是这可能会影响优化其选择执行计划,因此,建议手工直接编译这些失效的物化视图,如下 alter MATERIALIZED VIEW DH_T_MV compile; 备注,其实这步已经包含在依赖对象重建部分了,单独拿出来是因为这个依赖对象非常重要,不容有任何意外 
物化视图日志:物化视图日志是为了快速刷新准备的,而且从dba_dependencies 这张依赖表中无法查找出来的,但是,对于这个对象,我们一定要保持谨慎和敬畏,因为如果表上存在物化视图日志对象的话,那么这张表无法完成rename(在一个变更的晚上,其它什么都OK了,突然遇到一个这样的问题,还得找开发确认,是非常被动的,整个变更很有可能因为这个无法确认而取消),会直接报错,查找表上的物化视图日志对象方法如下:select master,log_table from user_mview_logs a where master in ('DH_T'); 

备注:1、我们可能还需要关注表字段类型,那些LOB、long字段都是我们重建表是需要考虑的2、还有就是重建表时我们可能都会使用parallel+nologging模式来加快速度,一定要记得在重建完成后将这些属性修改回来。(以前遇到过一个案例,未将parallel属性改回来,导致执行计划选用并行,最终导致资源很快耗尽,CPU100%)3、还有一些同步机制,如果同步依赖rowid,由于重建表rowid会该表,可能造成实时同步失败,这些都是我们需要考虑的最后,在工作完成后,检查一下所有对象的有效性是一个不错的方案。(建议在重建前保存快照,重建后与前面的快照比较)上面讲述的都是一些我们最常用的对象,其它一些很少使用的对象这里就不概述了


原文地址:http://www.2cto.com/database/201407/313687.html


分享给朋友:
您可能感兴趣的文章:
随机阅读: