Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Brisbane, Australia
    Posts
    2

    Question Unanswered: Analysis of views in Oracle 9i DB

    Hi all,

    I have been assigned to a team working on a bit of a situation and we have so far been unable to find a solution (we aren't wanting to write software to do this ourselves quite yet).

    The situation is that within our database (Oracle 9i) there are some 700 views that reference both tables in our database and also reference tables and columns in other databases in another section of the University. The issue is that none of these interactions are documented and we are attempting to find a tool, piece of software or function within Oracle 9i that is able to, from the SQL queries within the views determine which database, table and column the views are retrieving information from. The only tools that we have been able to locate so far are able to display the columns within each view but don't show where the information is retrieved from.

    We are virtually resigned to the fact that we will have to write some code ourselves to do this, but are firstly wondering if anyone else has been in a similar situation or knows of software that can do this ??

    Thanks in advance!!

    Regards,

    Andrew

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    take a look into user_views datadictionary view from oracle which contains the source text of your view in one of thoses columns

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    ALL_DEPENDENCIES will get you some of the way, but the remote tables might be trickier.

  4. #4
    Join Date
    Jan 2005
    Location
    Brisbane, Australia
    Posts
    2
    Thanks for the tips, tried those and doesn't work in our case unfortunately Most of the views are in a different database to the tables they are referencing, so it simply displays the link to the host.

    Thanks for the help.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You might find this useful as a starter. If you have 10g then you can use regular expressions to make it much better, or you may want to feed the results through something like perl.

    create table test as select view_name, dbms_metadata.get_ddl('VIEW',view_name,owner) txt from dba_views where owner='XXX'

    select t.*, u.* from
    dba_tables u,
    test t
    where upper(t.txt) like '%'||u.table_name||'%'
    and instr(upper(txt), u.table_name)>0
    and owner = 'XXX'
    order by view_name

    NOTE as you have remote databases union together the dba_tables@remotedb so it will search table names across all your databases.

    NOTE it is doing a rough guess i.e. it may not handle case sensitive table names correctly. Also if you two tables called 'abc' and 'abcd' then a view refering to 'abcd' will also show up as matching 'abc'.

    Alan

Posting Permissions

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