Oracle 对象依赖性与对象失效

一、 起源

开发A在从库查询视图遇到报错：

执行的SQL为: select * from V_TEST_JOIN 
具体错误信息为：java.sql.SQLSyntaxErrorException: 
ORA-04045: 在重新编译/重新验证 V_TEST_JOIN 时出错
ORA-16000: 数据库或可插入数据库是以只读访问方式打开的

二、 报错原因及处理方法

1. 报错原因

视图V_TEST_JOIN失效，在select时会自动编译，但访问的是从库，而从库是只读的，因此编译会报错ORA-16000: 数据库或可插入数据库是以只读访问方式打开的

2. 处理方法

报错处理方法极其简单，在主库编译失效对象即可。常见编译方法如下：

• 在主库查询该对象由其自动编译
• ALTER … COMPILE;手动编译

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

也可以生成重编译的批量脚本后一起执行：

SELECT 'ALTER '||OBJECT_TYPE||' '||OBJECT_NAME ||' COMPILE;'
  FROM USER_OBJECTS
 WHERE STATUS='INVALID';

• utlrp.sql脚本

执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象。

数据库的升级迁移等操作可能会导致数据库中大量对象失效，由于对象间可能存在复杂的依赖关系，手工编译通常无法顺利通过。Oracle强烈建议在升级迁移后，运行此脚本编译失效对象。utlrp.sql中调用了$ORACLE_HOME/rdbms/admin/utlrcmp.sql来编译失效对象

此脚本需要用SQLPLUS以SYSDBA身份运行，运行时中最好不要有活动事物或DDL操作，否则极容易导致死锁的出现。

• DBMS_DDL包 (只用于PL/SQL对象，不能编译视图):

EXEC DBMS_DDL('PACKAGE','MY_SCHEMA','MY_PACKAGE'); 
EXEC DBMS_DDL('PACKAGE BODY','MY_SCHEMA','MY_PACKAGE'); 
EXEC DBMS_DDL('PROCEDURE','MY_SCHEMA','MY_PROCEDURE'); 
EXEC DBMS_DDL('FUNCTION','MY_SCHEMA','MY_FUNCTION'); 
EXEC DBMS_DDL('TRIGGER','MY_SCHEMA','MY_TRIGGER');

• 使用DBMS_UTILITY.compile_schema

ORACLE提供了自动编译的接口，调用这个过程会编译指定schema下所有失效的视图、存储过程、函数、触发器、包

exec dbms_utility.compile_schema('SCOTT')

• UTL_RECOMP.RECOMP_PARALLEL、UTL_RECOMP.RECOMP_SERIAL分别用并行或串行方式进行编译

-- Schema level.EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4,'SCOTT');

 -- Database level.EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

 -- Using job_queue_processes value.EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL,'SCOTT');

UTL_RECOMP包的一些使用限制：

(1) 并行执行使用的是job队列，当运行并行编译的时候所有job都会被disable直到编译完成

(2) 包必须在sqlplus中以sysdba权限用户运行

(3) UTL_RECOMP依赖于DBMS_STANDARD、DBMS_JOB、DBMS_RANDOM

(4) 在运行这个包时执行DDL语句可能会导致死锁.

三、 如何找到对象失效时间及原因

1. 如果对象是因为编译报错失效的，可以用以下sql查出

select object_name, status, last_ddl_time, e.line, e.text
  from dba_objects d, dba_errors e
 where d.status = 'INVALID'
   and d.OBJECT_NAME = e.name
   and d.object_type = e.type
   and d.owner = e.owner

2. 如果是因为引用对象被修改

可以使用以下方法大概判断，不一定准确（但是也能压到一个较小的时间范围去定位）

思路：对象失效是由于其引用对象更改，可以通过DBA_DEPENDENCIES找到失效对象的引用对象，再由dba_objects找到引用对象的last_ddl_time (ldt1)，大于失效对象last_ddl_time (ldt2)并且离其最近的时间一般就是该对象失效的时间(ldt3)。去找ldt3时对应的ddl审计日志，对应sql语句一般就是导致对象失效的原因。

下面是个实际的例子

-- 启用enable_ddl_logging
SQL> show parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL> show parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

有一个失效对象V_TEST_JOIN，last_ddl_time (ldt2)为2019/9/26 15:21:58

查看其引用对象

select * from dba_dependencies d where d.name='V_TEST_JOIN';

查看引用对象last_ddl_time (ldt1)

select d.object_name,d.status,d.last_ddl_time from dba_objects d where d.OBJECT_NAME in('TEST01','TEST02');

可以看到大于ldt2且离其最近的时间ldt3是2019/9/26 15:23:38，这个时间应该就是对象真正失效的时间。

根据ldt3去找ddl日志

cd $ORACLE_BASE/diag/rdbms/conuat/conuat/log

less ddl_conuat.log
…
Thu Sep 26 15:23:38 2019
diag_adl:
alter table TEST02 add testcol number

所以失效的原因是基表TEST02加了列

这里有另外一个问题，在反向测试给表加列会不会导致视图失效的时候，发现基表加列后测试的视图并没有失效，甚至给V_TEST_JOIN视图基表TEST01加列的时候视图也不会失效。

为什么会有这种现象，这就需要去看对象失效究竟是怎么定义的，究竟什么操作才会导致对象失效。

这个答案我们放在最后。

四、 对象依赖性

1. 依赖关系概述

某些类型的对象可以在其定义中引用其他对象。如果对象A的定义引用了对象B，则A是（B的）依赖对象，而B是（A的）引用对象。（ If the definition of object A references object B, then A is a dependent object (of B) and B is a referenced object (of A).）

Oracle自动跟踪和管理依赖关系，以确保依赖对象A相对于其引用对象B而言始终是最新的。当B以可能影响A的方式更改时（通常是DDL语句），A会被标记为失效。在使用A之前，必须针对B的新定义重新编译失效的依赖对象。当引用失效的依赖对象时，它会自动重新编译。由于这种自动管理，A永远不会使用过时的B版本，并且在更改B之后几乎不需要显式重新编译A。

2. 依赖关系查看

通过USER_DEPENDENCIES，ALL_DEPENDENCIES和DBA_DEPENDENCIES视图查询对象间依赖关系

utldtree.sql脚本可创建视图DEPTREE和IDEPTREE。DEPTREE中包含对象依赖关系树的信息，而IDEPTREE则为格式化后的DEPTREE

3. 对象状态

注意：USER_OBJECTS，ALL_OBJECTS和DBA_OBJECTS不能区分“Compiled with errors、Invalid和“Unauthorized，而将这些状态统一描述为INVALID

五、 对象失效

1. 失效影响

对象失效通过以下方式影响应用程序：

• 失效对象必须先重新验证/编译才能被应用程序使用，会增加应用程序执行的延迟。如果失效对象的数量很大，则首次执行时增加的延迟可能会很大
• 存储过程、函数和包的失效可能会导致并发执行这些对象的其他会话异常（通常是在等待失效对象编译而被大量阻塞）
• 如果会话A运行应用程序的同时，在另一个会话B打了补丁，则执行应用程序的会话A会发现使用中的对象已失效，并引发以下4个异常之一：ORA-04061，ORA-04064，ORA-04065或ORA-04068，必须在重启应用程序会话来解决

2. 级联失效

如果对象A依赖于对象B，而对象B依赖于对象C，则A直接依赖于B，B直接依赖于C，而A间接依赖于C。A->B->C

• 对于直接依赖，只有更改了对影响它们的引用对象操作会使它们失效。
• 对于间接依赖，它们可能因为引用对象的失效而失效。例如更改C使B失效，则也将使A（还有B的所有直接和间接依赖对象）失效。这就是所谓的级联失效。

3. 粗粒度失效与细粒度失效

在11g前的版本，对象依赖项是在对象级别进行管理的，更改引用对象会自动使所有依赖对象失效，这称为粗粒度失效。11g开始引入了细粒度依赖管理新特性，更改引用对象时，只有直接影响依赖对象的更改才会导致依赖对象失效。

下面是会导致粗、细粒度失效的操作列表：

• 未在表中列出的所有DDL操作均会导致粗粒度失效
• 左列中的操作会导致细粒度的失效（操作对象不符合右列情况）
• 当执行左列操作且对象符合右列情况时，会导致粗粒度失效

六、 对象重新验证

失效的对象必须经过验证才能重新使用。访问对象时，验证会自动进行，不需用户操作。

1. 重新验证编译有错误的对象

编译器无法自动重新验证编译时出错的对象。使用编译器重新编译该对象，如果重新编译没有错误，将对其进行重新验证，否则它仍旧失效。

2. 重新验证未授权的对象

编译器检查依赖对象是否有对引用的访问权限。若有，编译器将重新验证未经授权的对象，而无需重新编译它，否则编译器会报错。

3. 重新验证失效的SQL对象

SQL编译器重新编译失效的对象。如果重新编译没有错误，将对其进行重新验证，否则它仍旧失效。

4. 重新验证失效的PL/SQL对象

对于失效的PL/SQL程序单元（过程，函数或程序包），PL/SQL编译器检查是否有引用对象以影响失效对象的方式进行更改。

• 若有，编译器将重新编译失效的对象。如果重新编译没有错误，将对其进行重新验证，否则它仍旧失效
• 若没有，编译器仅重新验证失效的对象而不进行重新编译

七、 疑问的答案

看到这里，我们已经找到了开头那个问题的答案。为什么给基表加列有时视图会失效有时不失效？

V_TEST的定义是

CREATE OR REPLACE VIEW V_TEST AS select * from test092502;

对于单表，加列不会导致视图失效（但是视图中也查不到新加列）

V_TEST_JOIN的定义是

CREATE OR REPLACE VIEW V_TEST_JOIN AS
SELECT a."ID",a."AGE",a."TESTCOL",b.name FROM TEST01 a left join TEST02 b on a.id=b.id;

所以给TEST02加列视图失效而给TEST01加列视图不失效

参考

https://docs.oracle.com/database/121/ADFNS/adfns_dependencies.htm#ADFNS99992

https://docs.oracle.com/database/121/ADMIN/general.htm#ADMIN12491

https://oracle-base.com/articles/11g/enhanced-finer-grained-dependency-management-11gr1