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 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.
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
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'.