Getting ORA-01031: insufficient privileges when creating a view that is refering to another schema's table

If you get PRA-0131 during creation of a view refering to a schema object in a different table, try the following:

grant select on foreing_schema.foreign_table to scott with grant option;

How to find dependencies between views and the tables they're based upon

Use the following Query to list the base tables on which the

select o.object_type,o.owner,o.object_name, CASE WHEN D.REFERENCED_NAME = O.OBJECT_NAME THEN NULL
                                            ELSE D.REFERENCED_NAME || '  --> ' || d.referenced_type 
                                            END "dependency to" 
from dba_objects o join dba_dependencies d
on o.object_name = d.name
where object_name in 
(
'MYVIEW1', 
'MYVIEW2',
) 
and object_type <> 'SYNONYM'
order by object_type desc, "dependency to";

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