Script to List Materialized Views Dependent on Specific Tables

This script will report on any materialized views that are dependent on a specific table.

REM LOCATION:   Object Management\Materialized Views and Materialized View Logs
REM FUNCTION:   Find dependent tables for a given MView
REM TESTED ON:  10.2.0.3, 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dbms_metadata
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET serveroutput on
SET feedback off

DECLARE
   v_output   VARCHAR2 (2000);
   v_owner    VARCHAR2 (30);
   v_table    VARCHAR2 (30);
BEGIN
   v_owner := UPPER ('&&ENTER_OWNER_NAME');
   v_table := UPPER ('&&ENTER_TABLE_NAME');
   dbms_mview.get_mv_dependencies (v_owner || '.' || v_table, v_output);
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line
      ('Materialized Views Dependent on table      &&ENTER_OWNER_NAME..&&ENTER_TABLE_NAME'
      );
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line (v_output);
END;
/

Sample Output

 

Materialized Views Dependent on table      SCOTT.EMP

"SCOTT"."MV_TEST","SCOTT"."MV_EMP" 

or

select * from dba_dependencies where referenced_name='MSC_TRADING_PARTNERS' and TYPE='MATERIALIZED VIEW';