Stale information in DBA_DEPENDENCIES

Recently I had to do some dependency analysis and subsequent removal of disused software when I noticed something strange in the DBA_DEPENDENCIES view. After dropping various database modules I could still see the old dependencies in DBA_DEPENDENCIES. This seemed strange as the dropped objects where not present in e.g. DBA_OBJECTS. So I decided to investigate this phenomenon to find out what is the cause.

I started by creating a simple table and function and two views. The first view selects from the table and the function. The second view selects from the first view.

VERSION                                                                          WHEN
-------------------------------------------------------------------------------- -------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production     10-12-2017 15:30:52
create table t1
(col1 number
)
;

Table created

create or replace function f1
(p1 in varchar2
)
return number
is
begin
  return 1;
end f1;
/

Function created

create or replace view v1
as
select col1
,      f1(col1) col2
from   t1
;

View created

create or replace view v2
as
select col1
,      col2
from   v1
;

View created

Then we query the dependencies op view V2.

select referenced_name
,      referenced_type
,      name
,      type
,      dependency_type
from   dba_dependencies
where  referenced_owner = 'MHF'
and    owner           <> 'PUBLIC'
and    name             = 'V2'
order by owner,name
;

REFERENCED_NAME REFERENCED_TYPE NAME TYPE DEPENDENCY_TYPE
--------------- --------------- ---- ---- ---------------
V1              VIEW            V2   VIEW HARD
F1              FUNCTION        V2   VIEW HARD

So we see that view V2 depends on V1 and apparently view V2 via view V1 depends on function F1. So this seems all ok.
Now suppose function F1 is not used anymore and needs to be dropped. In order to so this we have to change view V1 first and remove the call to the function.

create or replace view v1
as
select col1
,      cast(null as number) col2
from   t1
;

View created

So after this change I expected to see this change reflected in DBA_DEPENDENCIES. Let's see.

select referenced_name
,      referenced_type
,      name
,      type
,      dependency_type
from   dba_dependencies
where  referenced_owner = 'MHF'
and    owner           <> 'PUBLIC'
and    name             = 'V2'
order by owner,name
;

REFERENCED_NAME REFERENCED_TYPE NAME TYPE DEPENDENCY_TYPE
--------------- --------------- ---- ---- ---------------
V1              VIEW            V2   VIEW HARD
F1              FUNCTION        V2   VIEW HARD

Huh, this is strange, nothing has changed since the first time we queried the DBA_DEPENDENCIES view for view V2. The data still suggests that view V2 depends on function F1.

Ok, let's drop function F1. Certainly this must lead to a "refresh" of the information in DBA_DEPENDENCIES?

drop function f1
;

Function dropped

REFERENCED_NAME REFERENCED_TYPE NAME TYPE DEPENDENCY_TYPE
--------------- --------------- ---- ---- ---------------
V1              VIEW            V2   VIEW HARD
F1              NON-EXISTENT    V2   VIEW HARD

Still the information in DBA_DEPENDECIES shows the old dependency of view V2 on function F1, albeit this time the "REFERENCED_TYPE" has changed from "FUNCTION" to "NON-EXISTENT".

The information in DBA_DEPENDENCIES is still not correct. How do we fix this? Let's compile view V2.

alter view v2 compile
;

View altered

REFERENCED_NAME REFERENCED_TYPE NAME TYPE DEPENDENCY_TYPE
--------------- --------------- ---- ---- ---------------
V1              VIEW            V2   VIEW HARD

This has done the trick! The dependency information for view V2 is now finally correct.

Why is the information in DBA_DEPENDENCIES sometimes "stale". I think this is due to the fine grained dependency tracking that was introduced in version 11g. Altering view V1 to stop it using function F1 does not invalidate view V2. Even dropping function F1 does not invalidate view V2. Only after explicitly compiling view V2 the information in DBA_DEPENDENCIES is correct again.

I have performed this experiment on database version 11.2.0.4 and as shown above on 12.2.0.1. Both show the same behavior.

Something to keep in mind when doing dependency analysis.

Maybe it is a good idea to compile all program units before starting the dependency analysis. With the right settings this compile step also collects PL/Scope information that after compilation can be used in more fine grained dependency analysis.