Re: Obtaining advanced views metadata information from Oracle.

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 23 Mar 2005 21:21:27 +0000
Message-ID: <r6n341hsd64pk8g735jgjl836hltlglt3l@4ax.com>

On 23 Mar 2005 13:11:30 -0800, "Seba" <orav.20.sebastianpy_at_spamgourmet.com> wrote:

>For Oracle 9i (or 10i) I need to get advanced metadata information.
>
>I know how to get the DESC information of a view that will give me the
>columns, types, etc. But what I'm looking for is the *tables* that have
>the source information for the view.
>
>i.e. : if the following view was defined in my database:
>
>create view my_view as
>select parent_id, son_id
>from parent A, son B
>where A.parent_id = B.parent_id
>
>Is there any way that starting from my_view I can get the information
>that parent and son are the tables that are actually the source tables
>for the data that make up the view?

 all_dependencies holds this sort of information. Start from the view and do a hierarchical query on the tree of dependencies, then pick the tables from that lot.

select referenced_owner, referenced_name from (

    select referenced_owner, referenced_type, referenced_name     from all_dependencies

    connect by prior referenced_owner = owner
    and        prior referenced_type  = type
    and        prior referenced_name  = name
    start with owner = 'VIEW_OWNER'
    and        name = 'VIEW_NAME'

)
where referenced_type = 'TABLE'

 That should cope with views on views, and if you're calling functions then it'd get accessed tables from there too (excluding dynamic SQL).

>Another *different* question is how can I get the code that generates
>the view (the select query above). But please note that this will *not*
>solve my problem, I am working in a programmatic environment so I can't
>"parse" a bunch of SQL to find out which ones are the source tables.

 all_views.text

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_1202.htm#sthref1502

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Wed Mar 23 2005 - 15:21:27 CST