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...
DBA_MVIEWS
DBA_MVIEW_AGGREGATES
DBA_MVIEW_ANALYSIS
DBA_MVIEW_COMMENTS
DBA_MVIEW_DETAIL_PARTITION
DBA_MVIEW_DETAIL_RELATIONS
DBA_MVIEW_DETAIL_SUBPARTITION
DBA_MVIEW_JOINS
DBA_MVIEW_KEYS
DBA_MVIEW_LOGS
DBA_MVIEW_LOG_FILTER_COLS
DBA_MVIEW_REFRESH_TIMES
DBA_REGISTERED_MVIEWS
DBA_REGISTERED_MVIEW_GROUPS
DBA_TUNE_MVIEW