3

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.

1

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
  • I was hoping to find something more like Oracle documentation, but your analysis of the queries is great also. And I gather to be on the safe side is better to use public_dependencies, since it seems to always have more elements, right? – Dzyann May 12 '15 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.