Materialized Views

Check

Which views exist...

SET LINESIZE 200

SELECT owner, mview_name, query

FROM DBA_MVIEWS;

All materialized views tend to be owned by SYSTEM. The query text seems to be the only way to identify the owner of the underlying tables.

To identify materialized views which reference an object owned by a specific user, try...

SET SERVEROUTPUT ON

DECLARE

  l_var VARCHAR2(32767); -- max length

BEGIN

   FOR rec IN (SELECT mview_name, query FROM dba_mviews) LOOP

     l_var := rec.query;

     IF l_var LIKE '%&User%' 

     THEN 

        dbms_output.put_line(rec.mview_name || ' - ' || SUBSTR(rec.query,1,100));

     END IF;

   END LOOP;

END;

For the full text of materialized views which reference a specifc object try...

SET SERVEROUTPUT ON

DECLARE

  l_var VARCHAR2(32767); -- max length

BEGIN

   FOR rec IN (SELECT mview_name, query FROM dba_mviews) LOOP

     l_var := rec.query;

     IF l_var LIKE '%&Object%' 

     THEN 

        dbms_output.put_line(rec.mview_name || ' - ' || rec.query);

     END IF;

   END LOOP;

END;

Data Dictionary Views

ALL_ and USER_ versions of these views are also available...