Thursday, June 30, 2016

How to extract the base tables used by a view

To extract the tables being used in a view, you can query the USER_DEPENDENCIES view, like this:

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