RE: determine tables in view (9iR2)

From: cosmin ioan <cosmini_at_bridge-tech.com>
Date: Fri, 9 Feb 2007 14:16:50 -0800 (PST)
Message-ID: <284113.57457.qm@web60421.mail.yahoo.com>

thanks all ! thanks Tim!    

  dba_dependencies and of course, the plan.... ha.... the plan, would do it!   learning new stuff every day! the nice part is that the learning never ends so, I'll never get bored ;-) he he he       

Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:

      dba_dependencies?
  e.g. this query shows all the tables used by view DBA_SEGMENTS?    

  set linesize 100
column obj_level format a16
column obj format a40
column refobj format a40
select

   lpad (' ', 2 * (level - 1)) || to_char (level, '999') as obj_level,    owner || '.' || name || ' (' || type || ')' as obj,    referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')'

      as refobj
 from dba_dependencies
 start with owner = 'SYS' and name = 'DBA_SEGMENTS'  connect by prior referenced_owner = owner and prior referenced_name = name

    and prior referenced_type = type
    and type in ('TABLE', 'VIEW') ;     

---

  De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de cosmin ioan Envoyé : vendredi, 9. février 2007 12:20 À : oracle-l_at_freelists.org
Objet : re: determine tables in view (9iR2)   

  hi all,
  is there a (relatively) simple query or function to obtain all tables from a view, keeping in mind that that view can have many subviews which in turn could have many subviews etc etc (recursive func)    

  I don't want to re-invent the wheel nor write some funky long pl/sql, if something already exists out there ;-)    

  thx much,
  Cos

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 09 2007 - 16:16:50 CST