主页 / dba / 问题 / 100963
Accepted
Dzyann
Asked: 2015-05-09 05:21:47 +0800 CST

public_dependency 和 dba_dependencies 有什么区别？

  • 772

我知道PUBLIC_DEPENDENCY只有对象和引用 ID，而DBA_DEPENDENCIES没有 ID，但有类型、所有者、名称等。

但除此之外，两者之间有什么区别？当我对我的数据库进行计数时，它们返回不同的值。而PUBLIC_DEPENDENCY有更多的元素。

oracle oracle-10g

1 个回答

  1. Best Answer

    检查两个视图的来源：

    select dbms_metadata.get_ddl('VIEW', 'PUBLIC_DEPENDENCY', 'SYS') from dual;
select dbms_metadata.get_ddl('VIEW', 'DBA_DEPENDENCIES', 'SYS') from dual;

    PUBLIC_DEPENDENCY直接从字典表查询，仅此dependency$而已。 DBA_DEPENDENCIES也查询dependency$，但也加入一些其他表和视图，例如disk_and_fixed_objects。此视图不包含来自 的所有对象，因此此连接从与缺失对象相关的dependency$行中删除了一些行。dependency$例如在我的数据库中：

    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

    的来源disk_and_fixed_objects是：

      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

    但是上面的13个对象在上面的下划线视图或者X$表中并没有占，它们在另一个X$表中（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.

    KQFDTNAM在这里您可以在列中看到这些对象的名称。另一种找到它们的方法是查询V$FIXED_TABLE视图：

    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

    总结一下：DBA_DEPENDENCIES不包含一些固定对象相关的依赖。您还可以检查哪些对象与这些“缺失”依赖项相关，例如：

    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
    • 1

相关问题