Início / dba / Perguntas / 100963
Accepted
Dzyann
Asked: 2015-05-09 05:21:47 +0800 CST

Qual é a diferença entre public_dependency e dba_dependencies?

  • 772

Eu sei que PUBLIC_DEPENDENCY tem apenas os Ids dos objetos e referências, e o DBA_DEPENDENCIES não tem os Ids, mas o Tipo, Dono, Nome, etc deles.

Mas além disso, qual é a diferença entre ambos? Quando faço uma contagem no meu banco de dados, eles retornam valores diferentes. E PUBLIC_DEPENDENCY tem mais elementos.

oracle oracle-10g

1 respostas

  1. Best Answer

    Verifique a fonte de ambas as visualizações:

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

    PUBLIC_DEPENDENCYconsultas diretamente da tabela do dicionário dependency$e nada mais. DBA_DEPENDENCIEStambém consulta dependency$, mas também une algumas outras tabelas e exibições, por exemplo disk_and_fixed_objects. Esta visualização não contém todos os objetos de dependency$, portanto, esta junção elimina algumas linhas dependency$relacionadas aos objetos ausentes. Por exemplo no meu banco de dados:

    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

    A fonte de 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

    Mas os 13 objetos acima não são contabilizados na visão de sublinhado acima ou nas tabelas X$, eles estão em outra tabela 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.

    Aqui você pode ver o nome desses objetos na KQFDTNAMcoluna. Outra maneira de encontrá-los é consultando a V$FIXED_TABLEexibição:

    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

    Resumindo: DBA_DEPENDENCIESnão contém algumas dependências relacionadas a alguns objetos fixos. Você também pode verificar quais objetos estão relacionados a essas dependências "ausentes", por exemplo:

    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

relate perguntas