Any dictionary to list source tables of the view?

[mingichu]

Hi! all experts:

Does anyone know any dictionary table or good tool to list views and their source tables? The only thing I know is the TYPE_TEXT in USER_VIEWS, but it doesn't show the complete script for creating views and source table names are always missing.

Thanks in advance for your time and help!!

 

[vikramkalsi]

Hi,
You are probably not getting the complete source of the view because the column type of TEXT in USER_VIEWS is LONG and the default length of LONG displayed in Sqlplus is just 80. In sqlplus issue the command 'set long 4000' without the quotes, then query user_views :-
SQL> sho long
long 80
SQL> set long 4000
SQL>select text from user_views where view_name='view_name';

You will get the complete source of the view.

Hope it helps. Do let me know if it does.

 

[mingichu]

Thanks a lot!! It really helps!

 

[lewisp]

To see the tables referenced by a view, use

SELECT referenced_name
FROM all_dependencies
WHERE name = '<view_name>';