2 Replies Latest reply: Feb 7, 2013 12:39 PM by rp0428 RSS

    all_dependencies

    user650888
      If I want to know mytable is used in a select or insert everywhere across the database,

      I do not have access to dba_dependencies, Is the below correct ?

      I just need to know the dependent objects for mytable

      select name||'-->'||owner||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE||
      '-->'||REFERENCED_OWNER
      from all_dependencies
      where lower(NAME) like LOWER('%mytable%')
      and owner not in ('SYS','SYSTEM')
      order by name
        • 1. Re: all_dependencies
          riedelme
          user650888 wrote:
          If I want to know mytable is used in a select or insert everywhere across the database,

          I do not have access to dba_dependencies, Is the below correct ?

          I just need to know the dependent objects for mytable

          select name||'-->'||owner||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE||
          '-->'||REFERENCED_OWNER
          from all_dependencies
          where lower(NAME) like LOWER('%mytable%')
          and owner not in ('SYS','SYSTEM')
          order by name
          ALL_DEPENDENCIES will only cover the objects you have access to. If tables/views/whatever exist that you have no privileges for they will be missed by ALL_DEPENDENCIES.

          Most sites lock down the DBA views securely so you might have to work with the ALL_* views if you don't have DBA priveleges.
          • 2. Re: all_dependencies
            rp0428
            >
            If I want to know mytable is used in a select or insert everywhere across the database,

            I do not have access to dba_dependencies, Is the below correct ?

            I just need to know the dependent objects for mytable

            select name||'-->'||owner||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE||
            '-->'||REFERENCED_OWNER
            from all_dependencies
            where lower(NAME) like LOWER('%mytable%')
            and owner not in ('SYS','SYSTEM')
            order by name
            >
            Add a predicate 'and type = 'TABLE' if you only care about a table.

            And I suggest you start by just querying the view itself until you know you are getting the data you want. Then you can start concatenating everything together if you need to.

            Your query produces such a jumbled set of data it's hard to tell if it has the info you need. Start with
            select * from all_dependencies
            where lower(NAME) like LOWER('%employees%')
            and owner not in ('SYS','SYSTEM')
            and type = 'TABLE'
            order by name