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

最新推荐文章于 2021-01-22 18:10:32 发布

congjiu2607

最新推荐文章于 2021-01-22 18:10:32 发布

阅读量860

点赞数

文章标签：数据库

http://www.cnblogs.com/ljbguanli/p/6752029.html

一、概述

前一段时间，有一个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，否则创建的时候会出现索引名字已经存在的错误

例如以下查询当前表的索引并改名sql：

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');

备注：

我们可能还须要关注表字段类型，那些LOB、long字段都是我们重建表是须要考虑的
还有就是重建表是我们可能都会使用parallel+nologging模式来加高速度，一定要记得在重建完毕后将这些属性改动回来。（曾经遇到过一个案例，未将parallel属性改回来，导致运行计划选用并行，终于导致资源非常快耗尽，CPU100%）
另一些同步机制。假设同步依赖rowid，因为重建表rowid会该表。可能造成实时同步失败，这些都是我们须要考虑的
最后。在工作完毕后，检查一下全部对象的有效性是一个不错的方案。（建议在重建前保存快照。重建后与前面的快照比較）
上面讲述的都是一些我们最经常使用的对象，其他一些非常少使用的对象这里就不概述了