Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14

    Unanswered: table dependencies

    Hi,

    Before I update a base table, I need to investigate which other objects are going to be affected by this change.....i.e. I need to find out all the objects that are using this table. This could be recursive..in other words the object using this table could be used by other objects. I need to get down to the bottom of the tree.

    Could someone pleae suggest the best way of doing so. A readymade hierarchical query would be much appreciated.

    cheers

    P

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Give this a shot ....



    column r_name format a80
    column name format a80
    column r_link format a80
    set heading off

    select decode(referenced_type, 'NON-EXISTENT', '.....', referenced_type)
    || ' ' || referenced_owner || '.' || referenced_name r_name,
    ' is referenced by: ' || type || ' ' || owner || '.' || name name,
    ' Referenced Link: ' || decode(referenced_link_name, null, 'none',
    referenced_link_name) r_link
    from sys.dba_dependencies
    where referenced_name IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE upper('&object'))
    order by 1,2;


    HTH
    Gregg

  3. #3
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14
    Thanks Gregg!! It works

    But I need to run this recursively. I tried the following hierarchical query...

    select decode(referenced_type, 'NON-EXISTENT', '.....', referenced_type)
    || ' ' || referenced_owner || '.' || referenced_name r_name,
    ' is referenced by: ' || type || ' ' || owner || '.' || name name,
    ' Referenced Link: ' || decode(referenced_link_name, null, 'none',
    referenced_link_name) r_link
    from all_dependencies
    start with referenced_name = &TABLE_NAME
    connect by prior name = referenced_name
    order by 1,2;


    ...without any success. Apparently, you can't run a connect by query on a view in Oracle 8.1.7.

    So I guess I am gonna have to run your query a number of times to get to the bottom of the list.

    Can you think of another efficient way?

    cheers

    P

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try deptree


    /* @c:\oracle\ora81\rdbms\admin\utldtree must be run under sys */

    DEFINE object_type = '&1'
    DEFINE object_owner = '&2'
    DEFINE object_name = '&3'

    execute deptree_fill(upper('&object_type'),upper('&object_ owner'),upper('&object_name'));

    select * from ideptree;

    This may take a little time to run ...

    HTH
    Gregg

  5. #5
    Join Date
    Sep 2012
    Posts
    1

    ODA - Oracle Dependencies Analyzer

    Hi
    Have a look this tools:

    "The ODA tool has been written to analyze database dependencies between database objects such as procedures, tables, views etc. The tool can also be used to analyze database dependencies between objects in Forms/Report PL/SQL code and block properties and the database objects dependencies also. The ODA find object usage in the flat files(Unix scripts,C,XML) ,Informatica workflows and Word files"

    regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •