Materialized View Definitions (dmview.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive information about materialized views in the database, including their refresh characteristics, query rewrite capabilities, current staleness status, and compilation state. It helps DBAs manage and troubleshoot materialized views used for performance optimization and data warehousing.

Script

rem
rem dmview.sql
rem
ttitle 'Materialized Views'
rem
set linesize 132
set long 1000
rem
col owner format a12 heading 'OWNER'
col MVIEW_NAME format a22 heading 'MVIEW'
col CONTAINER_NAME format a22 heading 'CONTAINER'
 -- QUERY                               LONG
 -- QUERY_LEN                           NUMBER(38)
 -- UPDATABLE                           VARCHAR2(1)
 -- UPDATE_LOG                          VARCHAR2(30)
col MASTER_ROLLBACK_SEG format a10 head 'ROLLBACK'
col MASTER_LINK format a11 head 'MASTER LINK'
 -- REWRITE_ENABLED                     VARCHAR2(1)
 -- REWRITE_CAPABILITY                  VARCHAR2(9)
 -- REFRESH_MODE                        VARCHAR2(6)
 -- REFRESH_METHOD                      VARCHAR2(8)
 -- BUILD_MODE                          VARCHAR2(9)
 -- FAST_REFRESHABLE                    VARCHAR2(18)
 -- LAST_REFRESH_TYPE                   VARCHAR2(8)
 -- LAST_REFRESH_DATE                   VARCHAR2(9)
 -- STALENESS                           VARCHAR2(9)
 -- AFTER_FAST_REFRESH                  VARCHAR2(9)
 -- COMPILE_STATE                       VARCHAR2(13)
rem
select
 OWNER,
 MVIEW_NAME,
 CONTAINER_NAME,
 -- QUERY
 -- QUERY_LEN,
 UPDATABLE,
 UPDATE_LOG,
 MASTER_ROLLBACK_SEG,
 MASTER_LINK,
 REWRITE_ENABLED,
 REWRITE_CAPABILITY,
 REFRESH_MODE,
 REFRESH_METHOD,
 BUILD_MODE,
 FAST_REFRESHABLE,
 LAST_REFRESH_TYPE,
 LAST_REFRESH_DATE,
 STALENESS,
 AFTER_FAST_REFRESH,
 COMPILE_STATE
from dba_mviews
where owner like nvl(upper('&owner'), '%')
  and mview_name like nvl(upper('&mview'), '%')
/

Usage

-- Run the script in SQL*Plus or SQLcl
@dmview.sql

-- When prompted, enter parameters:
-- owner: Schema owner (% for all)
-- mview: Materialized view name pattern (% for all)

-- Examples:
Enter value for owner: DW
Enter value for mview: %

-- Find specific materialized view
Enter value for owner: SALES
Enter value for mview: MONTHLY_SUMMARY

Parameters

• &owner - Schema owner (supports wildcards)
• &mview - Materialized view name pattern

Required Privileges

• SELECT on DBA_MVIEWS
• Typically requires DBA role or SELECT ANY DICTIONARY

Sample Output

Materialized Views

OWNER        MVIEW                  CONTAINER              U UPDATE_LOG  ROLLBACK   MASTER LINK R REWRITE_C REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE   LAST_REF LAST_REFR STALENESS AFTER_FAS COMPILE_STATE
------------ ---------------------- ---------------------- - ----------- ---------- ----------- - --------- ------ -------- --------- ------------------ -------- --------- --------- --------- -------------
DW           SALES_SUMMARY          SALES_SUMMARY          N                                    Y GENERAL   DEMAND COMPLETE IMMEDIATE DML                COMPLETE 06-JAN-24 FRESH     FRESH     VALID
DW           PRODUCT_AGG            PRODUCT_AGG            N                                    Y TEXTMATCH DEMAND FAST     DEFERRED  DIRLOAD_DML        FAST     06-JAN-24 FRESH     FRESH     VALID
DW           CUSTOMER_MONTHLY       CUSTOMER_MONTHLY       N                       DB_LINK1    N NONE      COMMIT FAST     IMMEDIATE DIRLOAD_LIMITEDDML FAST     06-JAN-24 NEEDS_COM UNKNOWN   NEEDS_COMPILE
SALES        REGION_TOTALS          REGION_TOTALS          Y REGION_LOG                        Y GENERAL   DEMAND FORCE    IMMEDIATE DML                FORCE    05-JAN-24 STALE     STALE     VALID

Key Output Columns

• OWNER - Schema owning the materialized view
• MVIEW - Materialized view name
• CONTAINER - Container table name
• U (UPDATABLE) - Whether MV is updatable (Y/N)
• UPDATE_LOG - Update log table name
• ROLLBACK - Rollback segment for refresh
• MASTER LINK - Database link for remote master
• R (REWRITE_ENABLED) - Query rewrite enabled (Y/N)
• REWRITE_C - Rewrite capability level
• REFRESH - Refresh mode (DEMAND/COMMIT)
• REFRESH_METHOD - Method (COMPLETE/FAST/FORCE)
• BUILD_MODE - Build mode (IMMEDIATE/DEFERRED)
• FAST_REFRESHABLE - Fast refresh restrictions
• LAST_REFRESH_TYPE - Type of last refresh
• LAST_REFRESH_DATE - Date of last refresh
• STALENESS - Current staleness status
• AFTER_FAST_REFRESH - Status after fast refresh
• COMPILE_STATE - Compilation status

Understanding Materialized Views

Refresh Methods

• COMPLETE - Full rebuild of MV
• FAST - Incremental refresh using logs
• FORCE - Fast if possible, otherwise complete

Refresh Modes

• DEMAND - Manual refresh required
• COMMIT - Automatic refresh on commit

Staleness States

• FRESH - Up to date with base tables
• STALE - Base tables have changed
• NEEDS_COMPILE - Requires recompilation
• UNKNOWN - Status cannot be determined

Common Use Cases

Find Stale Materialized Views

SELECT owner, mview_name, last_refresh_date,
       ROUND(SYSDATE - last_refresh_date) days_old
FROM dba_mviews
WHERE staleness IN ('STALE', 'NEEDS_COMPILE')
ORDER BY last_refresh_date;

Check Query Rewrite Capabilities

SELECT owner, mview_name, rewrite_enabled,
       rewrite_capability, compile_state
FROM dba_mviews
WHERE rewrite_enabled = 'Y'
AND compile_state != 'VALID';

Find Fast Refresh Problems

SELECT owner, mview_name, fast_refreshable,
       last_refresh_type, last_refresh_date
FROM dba_mviews
WHERE refresh_method = 'FAST'
AND fast_refreshable != 'DML'
ORDER BY owner, mview_name;

Refresh Management

Manual Refresh

-- Complete refresh
BEGIN
   DBMS_MVIEW.REFRESH('SCHEMA.MV_NAME', 'C');
END;
/

-- Fast refresh
BEGIN
   DBMS_MVIEW.REFRESH('SCHEMA.MV_NAME', 'F');
END;
/

-- Force refresh (fast if possible)
BEGIN
   DBMS_MVIEW.REFRESH('SCHEMA.MV_NAME', '?');
END;
/

Refresh Multiple MVs

-- Refresh a group of MVs
BEGIN
   DBMS_MVIEW.REFRESH(
      list => 'SALES_SUMMARY,PRODUCT_AGG,CUSTOMER_MONTHLY',
      method => 'F'
   );
END;
/

Check Refresh Jobs

-- Find MV refresh jobs
SELECT job, what, last_date, next_date, failures
FROM dba_jobs
WHERE what LIKE '%DBMS_MVIEW.REFRESH%'
ORDER BY next_date;

Troubleshooting

Compilation Errors

-- Recompile invalid MVs
ALTER MATERIALIZED VIEW owner.mview_name COMPILE;

-- Check for errors
SELECT owner, mview_name, compile_state
FROM dba_mviews
WHERE compile_state = 'COMPILATION_ERROR';

Fast Refresh Eligibility

-- Check why fast refresh not possible
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('owner.mview_name');

SELECT capability_name, possible, related_text, msgtxt
FROM mv_capabilities_table
WHERE mvname = 'MVIEW_NAME'
AND capability_name LIKE 'REFRESH_FAST%';

MV Log Issues

-- Check MV logs
SELECT log_owner, master, log_table,
       rowids, primary_key, object_id,
       filter_columns, include_new_values
FROM dba_mview_logs
WHERE log_owner = '&owner';

Performance Optimization

Query Rewrite

-- Enable query rewrite
ALTER MATERIALIZED VIEW owner.mview_name ENABLE QUERY REWRITE;

-- Check rewrite usage
SELECT * FROM v$sql_rewrite
WHERE mv_name = 'MVIEW_NAME';

Prebuilt Tables

-- Create MV on existing table
CREATE MATERIALIZED VIEW mv_name
ON PREBUILT TABLE
REFRESH FAST ON DEMAND
AS SELECT...;

Best Practices

Design Considerations

1. Use fast refresh for frequently updated MVs
2. Enable query rewrite for data warehouse MVs
3. Schedule refreshes during low-activity periods
4. Monitor staleness and refresh performance

Maintenance

• Regular compilation checks
• Monitor refresh job failures
• Clean up unused MV logs
• Document refresh dependencies

Related Scripts

• Database Objects (dobject.md) - Object overview
• Invalid Objects (../administration/dinvalid.md) - Find invalid MVs
• Scheduled Jobs (../administration/djobs.md) - MV refresh jobs

Advanced Features

Refresh Groups

-- Create refresh group
BEGIN
   DBMS_REFRESH.MAKE(
      name => 'SALES_REFRESH_GROUP',
      list => 'SALES_SUMMARY,SALES_DETAIL',
      next_date => SYSDATE,
      interval => 'SYSDATE + 1'
   );
END;
/

PCT Refresh

-- Check PCT refresh capability
SELECT owner, mview_name,
       last_refresh_type,
       staleness,
       after_fast_refresh
FROM dba_mviews
WHERE fast_refreshable LIKE '%PCT%';