How can I find the underlying column and table name for an Oracle view?

Answer 1

There isn't a way, because the definition of each view column is an expression, not (in general) merely a table column. For example, your view's SQL could be:

SELECT 
   UPPER(ENAME) || 'xxx',
   myfunction(DNAME)
FROM
   emp a,
   dept b
WHERE
   a.deptno= b.deptno

or perhaps

SELECT ename || 'xxx', dname
FROM (
  SELECT 
     UPPER(ENAME) AS ename,
     myfunction(DNAME) AS dname
  FROM
     emp a,
     dept b
  WHERE
     a.deptno= b.deptno
)

What would you expect to see for the "underlying columns" in this example?

Answer 2

The sql that defined the view can be found in all_views

set long 9999 
select TEXT from all_views where VIEW_NAME='MYVIEW';

This is the only way to get at the underlying tables and columns.

Answer 3

In 11g Oracle introduced finer grained dependency tracking. So the database knows which table columns a view or package body depends on. However, they don't seem to have exposed this data in a view. But there may be x$ tables with the info.

Answer 4

The linked procedures may be of some help for identifying dependencies

The DBA_DEPENDENCIES View will give you a list of the tables that a View is based on:

SELECT *
  FROM DBA_DEPENDENCIES
 WHERE OWNER = <Schema>
   AND NAME = <View_Name>
   AND TYPE = 'VIEW'