Q:

Find all the tables a view depends on

I have got an Oracle VIEW and I would like to find all the tables my view depends on.
Possibly, my view depends on other view: in this case, I would like to recursively navigate the dependencies and get to the tables.
Here is an example of my schema:
CREATE TABLE T1 (A NUMBER);
CREATE TABLE T2 (B NUMBER);
CREATE TABLE T3 (A NUMBER, B NUMBER);

CREATE VIEW V1 AS SELECT * FROM T1;
CREATE VIEW V2 AS SELECT * FROM T2;
CREATE VIEW V3 AS SELECT * FROM V1, V2 UNION ALL SELECT * FROM T3;

And this is the output I would like to get:
VIEW_NAME TABLE NAME
--------- ----------
V3        T1
V3        T2
V3        T3

A:

The DBA/USER/ALL_DEPENDENCIES system view has this information. http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1066.htm#i1576452
You can query it recursively using a CONNECT BY clause in the SELECT

