SELECT NAME, TYPE, REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES Where Type = 'VIEW' And Name In ( 'V_FEMALE_ARTIST','VW_ALBUM');
Output:
NAME TYPE REFERENCED_NAME REFERENCED_TYPE ---------------------------------------------------------------------------------------------------- VW_ALBUM VIEW ALBUM TABLE VW_ALBUM VIEW SONGS TABLE V_FEMALE_ARTIST VIEW ARTIST TABLE
The column REFERENCED_NAME holds the information you are looking for.
I can see that my view VW_ALBUM is based on the tables ALBUM and SONG.
The last view uses no JOIN in its definition, and is based on one single table only.
Note what happens if you create a view based on the data dictionary, for example:
create or replace view mytables (tab_name, created, tabsize) AS SELECT t.table_name,to_char(o.created, 'dd.mm.yyyy'),sum(s.bytes) from user_tables t join user_objects o on t.table_name = o.object_name join user_segments s on s.segment_name = t.table_name where o.object_type = 'TABLE' group by t.table_name, o.created order by 2, 3 desc; View created.
When you query the USER_DEPENDENCIES again, and consider the newly created view, note that the source of the view are not tables, but synonyms (and since I am using the view USER_SEGMENTS, I am consequently also depending on the DBMS_SPACE_ADMIN package):
SELECT NAME, TYPE, REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES Where Type = 'VIEW' And Name In ( 'V_FEMALE_ARTIST','VW_ALBUM','MYTABLES') ORDER BY NAME;
NAME TYPE REFERENCED_NAME REFERENCED_TYPE ---------------------------------------------------------------------------------------------------- MYTABLES VIEW USER_OBJECTS SYNONYM MYTABLES VIEW USER_TABLES SYNONYM MYTABLES VIEW DBMS_SPACE_ADMIN PACKAGE MYTABLES VIEW USER_SEGMENTS SYNONYM VW_ALBUM VIEW ALBUM TABLE VW_ALBUM VIEW SONGS TABLE V_FEMALE_ARTIST VIEW ARTIST TABLE
No comments:
Post a Comment