Query the SQL which drives an Oracle view.

If you’ve ever wanted to find out which views reference a certain table/column condition in an Oracle database, there is a view called user_views which can be queried as the owner of the view and contains the sql used to define the population of the view. There is a slight trick to querying it though, as that column is a Long datatype. You can use the following SQL:

select * from user_views
where dbms_xmlgen.getxml('select text from user_views 
                          where view_name = ''' || view_name || '''') 
                         like '%<thing I want to search for>%';

Just replace the like with the thing you want to search for. Hope that’s useful!