I know that PUBLIC_DEPENDENCY has only the objects and references Ids, and the DBA_DEPENDENCIES does not have the Ids, but the Type, Owner, Name, etc of them.
But beyond that, what is the difference between both? When I do a count on my database, they return different values. And PUBLIC_DEPENDENCY has more elements.
Check the source of both views:
select dbms_metadata.get_ddl('VIEW', 'PUBLIC_DEPENDENCY', 'SYS') from dual;
select dbms_metadata.get_ddl('VIEW', 'DBA_DEPENDENCIES', 'SYS') from dual;
PUBLIC_DEPENDENCY queries directly from the dictionary table dependency$ and nothing else.
DBA_DEPENDENCIES also queries dependency$, but joins some other tables and views as well, for example disk_and_fixed_objects. This view does not contain all the objects from dependency$, so this join eliminates some rows from dependency$ related to the missing objects. For example on my database:
select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#);
P_OBJ#
----------
4294951597
4294952371
4294952370
4294952376
4294952374
4294952368
4294952372
4294952375
4294951440
4294952364
4294952365
4294952366
4294952373
The source of disk_and_fixed_objects is:
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DISK_AND_FIXED_OBJECTS" ("OBJ#", "OWNER#", "NAME", "TYPE#", "REMOTEOWNER", "LINKNAME", "EDITIONNAME") AS
select co.obj#, co.owner#, co.name, co.type#, co.remoteowner, co.linkname,
co.defining_edition
from sys."_CURRENT_EDITION_OBJ" co
union all
select kobjn_kqfp, 0, name_kqfp,
decode(type_kqfp, 1, 9, 2, 11, 3, 10, 0), NULL, NULL, NULL
from sys.x$kqfp
union all
select kqftaobj, 0, kqftanam, 2, NULL, NULL, NULL
from sys.x$kqfta
union all
select kqfviobj, 0, kqfvinam, 4, null, null, null
from sys.x$kqfvi
But the above 13 objects are not accounted for in the above underscore view or X$ tables, they are in another X$ table (X$KQFDT):
select * from x$kqfdt where kqfdtobj in ( select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));
ADDR INDX INST_ID CON_ID KQFDTOBJ KQFDTNAM KQFDTEQU
---------------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------
000000000DF41A00 4 1 0 4294951440 X$KCVFHALL X$KCVFH
000000000DF41C80 20 1 0 4294951597 X$JOXFS X$JOXFT
000000000DF41D20 24 1 0 4294952364 X$JOXOBJ X$JOXFT
000000000DF41D48 25 1 0 4294952365 X$JOXSCD X$JOXFT
000000000DF41D70 26 1 0 4294952366 X$JOXRSV X$JOXFT
000000000DF41DC0 28 1 0 4294952368 X$JOXDRC X$JOXFT
000000000DF41E10 30 1 0 4294952370 X$JOXMOB X$JOXFM
000000000DF41E38 31 1 0 4294952371 X$JOXMIF X$JOXFM
000000000DF41E60 32 1 0 4294952372 X$JOXMIC X$JOXFM
000000000DF41E88 33 1 0 4294952373 X$JOXMFD X$JOXFM
000000000DF41EB0 34 1 0 4294952374 X$JOXMMD X$JOXFM
000000000DF41ED8 35 1 0 4294952375 X$JOXMAG X$JOXFM
000000000DF41F00 36 1 0 4294952376 X$JOXMEX X$JOXFM
13 rows selected.
Here you can see the name of these objects in the KQFDTNAM column. Another way to find them is querying the V$FIXED_TABLE view:
select * from v$fixed_table where object_id in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));
NAME OBJECT_ID TYPE TABLE_NUM CON_ID
------------------------------ ---------- ----- ---------- ----------
X$KCVFHALL 4294951440 TABLE 65537 0
X$JOXFS 4294951597 TABLE 65537 0
X$JOXOBJ 4294952364 TABLE 65537 0
X$JOXSCD 4294952365 TABLE 65537 0
X$JOXRSV 4294952366 TABLE 65537 0
X$JOXDRC 4294952368 TABLE 65537 0
X$JOXMOB 4294952370 TABLE 65537 0
X$JOXMIF 4294952371 TABLE 65537 0
X$JOXMIC 4294952372 TABLE 65537 0
X$JOXMFD 4294952373 TABLE 65537 0
X$JOXMMD 4294952374 TABLE 65537 0
X$JOXMAG 4294952375 TABLE 65537 0
X$JOXMEX 4294952376 TABLE 65537 0
To sum up: DBA_DEPENDENCIES does not contain some dependencies related to some fixed objects. You can also check which objects are related to these "missing" dependencies, e.g.:
select o.owner, o.object_name, o.object_type from dependency$ d join dba_objects o on (d.d_obj# = o.object_id) where p_obj# in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#))
OWNER OBJECT_NAME OBJECT_TYPE
----- -------------------------------- -----------------------
SYS USER_SOURCE_AE VIEW
SYS ALL_SOURCE_AE VIEW
SYS DBA_SOURCE_AE VIEW
SYS INT$DBA_SOURCE VIEW
SYS KU$_JAVA_SOURCE_VIEW VIEW
SYS DBMS_PITR PACKAGE BODY
SYS USER_JAVA_CLASSES VIEW
SYS ALL_JAVA_CLASSES VIEW
SYS DBA_JAVA_CLASSES VIEW
SYS USER_JAVA_LAYOUTS VIEW
SYS ALL_JAVA_LAYOUTS VIEW
SYS DBA_JAVA_LAYOUTS VIEW
SYS USER_JAVA_IMPLEMENTS VIEW
SYS ALL_JAVA_IMPLEMENTS VIEW
SYS DBA_JAVA_IMPLEMENTS VIEW
SYS USER_JAVA_INNERS VIEW
SYS ALL_JAVA_INNERS VIEW
SYS DBA_JAVA_INNERS VIEW
SYS USER_JAVA_FIELDS VIEW
SYS ALL_JAVA_FIELDS VIEW
SYS DBA_JAVA_FIELDS VIEW
SYS USER_JAVA_METHODS VIEW
SYS ALL_JAVA_METHODS VIEW
SYS DBA_JAVA_METHODS VIEW
SYS USER_JAVA_ARGUMENTS VIEW
SYS ALL_JAVA_ARGUMENTS VIEW
SYS DBA_JAVA_ARGUMENTS VIEW
SYS USER_JAVA_THROWS VIEW
SYS USER_JAVA_THROWS VIEW
SYS ALL_JAVA_THROWS VIEW
SYS ALL_JAVA_THROWS VIEW
SYS DBA_JAVA_THROWS VIEW
SYS DBA_JAVA_THROWS VIEW
SYS USER_JAVA_DERIVATIONS VIEW
SYS ALL_JAVA_DERIVATIONS VIEW
SYS DBA_JAVA_DERIVATIONS VIEW
SYS USER_JAVA_RESOLVERS VIEW
SYS ALL_JAVA_RESOLVERS VIEW
SYS DBA_JAVA_RESOLVERS VIEW
SYS USER_JAVA_NCOMPS VIEW
SYS ALL_JAVA_NCOMPS VIEW
SYS DBA_JAVA_NCOMPS VIEW
