Oracle 重建表(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会该表,可能造成实时同步失败,这些都是我们需要考虑的
  4. 最后,在工作完成后,检查一下所有对象的有效性是一个不错的方案。(建议在重建前保存快照,重建后与前面的快照比较)
  5. 上面讲述的都是一些我们最常用的对象,其它一些很少使用的对象这里就不概述了

Oracle 重建表(rename)注意事项总结,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。