数据库无效对象

背景：报“ORACLE存在无效对象，可能影响应用性能，评估是否重建对象”

概念

  • 无效：INVALID
  • 对象：视图、物化视图、函数、包、触发器等

无效 PL/SQL 对象和不可用索引会对性能产生影响。无效 PL/SQL 对象必须先进行重编译，然后才能使用。这需要在执行尝试访问 PL/SQL 程序包、过程或函数的第一个操作之前花费一段编译时间。如果 PL/SQL 重编译未成功，，则操作会因发生错误而失败。优化程序会忽略不可用索引。如果 SQL 语句性能的好坏取决于已标记为不可用的索引，则只有重建索引才能改善性能。

如果查找到 INVALID 状态的 PL/SQL 对象，需要回答的第一个问题是“此对象曾经是 VALID 的吗？”应用程序开发人员常常会忘记清除不起作用的代码。如果 PL/SQL 对象因代码错误而无效，则除了纠正该错误之外，没有什么别的方法。如果该过程在过去曾经有效，最近才变为无效，则可选择以下两种方法解决这个问题：

1.不做任何处理。如果需要，大多数 PL/SQL 对象在调用时会自动重新编译。用户在对象重新编译时会经历短暂的延迟。（大多数情况下，这种延迟不十分明显。）

2.手动重新编译无效对象。

无效对象产生的原因

  • 当被引用对象的结构变更时，都会使得相关的依赖对象转变为INVALID状态

    如视图A来源于表B，当表B的结构改变时，会导致视图A变成无效对象。但当再一次调用视图A时，会自动编译视图A，使其再次变为有效。

  • 发布SQL脚本时（包、存储过程、函数等），没有充分测试，编译时出错，这时对象变为无效。

  • 数据库升级、迁移时，出现大量无效对象(本质原因，臆测归结为原因1)

  • 诸如此类各种情况：例如，Oracle 会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。

查找无效对象

  • 统计失效对象

执行用户：SYSTEM用户或其他有权限的用户

1
2
3
4

SQL> select owner, object_name, object_type
from dba_objects t
where status = 'INVALID'
order by t.owner,t.object_type;

或者

1
2
3
4
5

select owner, object_type, status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type
  • 查看具体失效对象
1
2
3
4
5
6
7
8

col owner for a20;
col object_name for a32;
col object_type for a16
col status for a8
select owner, object_name, object_type, status
from dba_objects
where status='INVALID'
order by 1,2,3;

解决

重新编译失效的对象（以下步骤参考：https://developer.aliyun.com/article/34172?spm=5176.24320532.content1.1.1b6b12c2lTMMVr#/）

  • 方法1：手工编译（针对少量对象失效情况）

    image-20220514214933040

  • 方法2：利用utlrp.sql（执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本）脚本编译无效对象

    该脚本可以在数据库运行的状态下执行编译invalid对象；建立在数据库进行迁移、升级、降级后都运行一遍utlrp.sql

    1
2
3
4

    oracle用户下执行
$cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba
SQL>@utlprp.sql

    许多情况下，由于数据库的升级或迁移，会导致数据库中的对象失效。由于对象 之间可能存在复杂的倚赖关系，所以手工编译通常无法顺利通过。通常我们会在Oracle的升级指导中看到这个脚本，Oracle强烈推荐在 migration/upgrade/downgrade之后，通过运行此脚本编译失效对象。但是注意,Oracle提醒，此脚本需要用SQLPLUS以 SYSDBA身份运行，并且当时数据库中最好不要有活动事物或DDL操作，否则极容易导致死锁的出现（这是很容易理解的）。

  • 方法3：利用ORACLE提供的自动编译接口dbms_utility.compile_schema(user,false);

    调用这个过程就会编译所有失效的过程、函数、触发器、包

    1

    exec dbms_utility.compile_schema( 'SCOTT' )

  • 方法4：利用网友提供的sql解决（例如 https://developer.aliyun.com/article/34172?spm=5176.24320532.content1.1.1b6b12c2lTMMVr#/ 给出的）

  • ps.也有可能是因为创建函数或存储过程等，里边的内容书写不正确导致。对于这种编译错误只需要找到编译报错位置，根据错误提示进行修改并重新编译即可。参考 https://blog.csdn.net/u010931042/article/details/103609979#/

参考链接

查询无效对象结果的例子以及解决例子：

​ https://www.shuzhiduo.com/A/A7zg7WNWz4/#/

​ https://blog.csdn.net/weixin_39617318/article/details/116344253#/

​ https://blog.csdn.net/weixin_30661579/article/details/116439657#/