Skip to content
Oracle DBA Scripts & Database Utilities

Materialized View Query Analysis (dmviewq.sql)

What This Script Does

Section titled “What This Script Does”

This script provides Oracle database administration functionality via the dmviewq.sql script.

The Script

Section titled “The Script”
rem
rem dmview.sql
rem
ttitle 'Materialized Views'
rem
set linesize 132
set long 2000000000
rem
col owner format a12 heading 'OWNER'
col MVIEW_NAME format a22 heading 'MVIEW'
col CONTAINER_NAME format a22 heading 'CONTAINER'
col QUERY format a92 word_wrap heading 'QUERY'
 -- 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_LEN,
 QUERY
from dba_mviews
where owner like nvl(upper('&owner'), '%')
  and mview_name like nvl(upper('&mview'), '%')
order by owner, mview_name
/

What This Script Does

Section titled “What This Script Does”

The dmviewq.sql script analyzes materialized view definitions and displays their underlying SQL queries. It provides a comprehensive view of materialized views in the database, showing ownership, name, and the complete query definition that defines each materialized view.

Usage

Section titled “Usage”

Run the script and provide input when prompted:

SQL> @dmviewq.sql
Enter value for owner: HR
Enter value for mview: SALES_

Parameters:

  • owner: Schema owner (use % for wildcard matching, or press Enter for all)
  • mview: Materialized view name pattern (use % for wildcard matching, or press Enter for all)

Required Privileges

Section titled “Required Privileges”
  • SELECT privilege on DBA_MVIEWS (usually requires DBA role or specific grants)
  • For non-DBA users, you can modify the script to use USER_MVIEWS or ALL_MVIEWS

Sample Output

Section titled “Sample Output”
OWNER        MVIEW                  QUERY
------------ ---------------------- ---------------------------------------------------------------------
HR           SALES_SUMMARY_MV       SELECT
                                      EXTRACT(YEAR FROM ORDER_DATE) AS YEAR,
                                      EXTRACT(MONTH FROM ORDER_DATE) AS MONTH,
                                      SUM(TOTAL_AMOUNT) AS TOTAL_SALES,
                                      COUNT(*) AS ORDER_COUNT
                                    FROM ORDERS
                                    GROUP BY EXTRACT(YEAR FROM ORDER_DATE),
                                             EXTRACT(MONTH FROM ORDER_DATE)


SALES        CUSTOMER_STATS_MV      SELECT
                                      CUSTOMER_ID,
                                      COUNT(*) AS ORDER_COUNT,
                                      SUM(ORDER_TOTAL) AS TOTAL_SPENT,
                                      AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE
                                    FROM CUSTOMER_ORDERS
                                    GROUP BY CUSTOMER_ID

Key Output Columns

Section titled “Key Output Columns”
  • OWNER: Schema that owns the materialized view
  • MVIEW: Name of the materialized view
  • QUERY: Complete SQL query definition that defines the materialized view content

Understanding the Results

Section titled “Understanding the Results”

Query Analysis

Section titled “Query Analysis”
  • Complex Aggregations: Look for GROUP BY, SUM, COUNT, AVG functions that indicate summary data
  • Join Operations: Identify multi-table joins that may benefit from materialized view optimization
  • Filter Conditions: Note WHERE clauses that limit data scope

Performance Considerations

Section titled “Performance Considerations”
  • Large Result Sets: Materialized views with complex queries can significantly improve query performance
  • Refresh Strategy: Consider the frequency of base table changes vs. query frequency
  • Storage Requirements: Large materialized views require substantial storage space

Common Use Cases

Section titled “Common Use Cases”

  1. Performance Analysis

    • Identify complex queries that benefit from pre-computation
    • Analyze aggregation patterns in reporting applications

  2. Query Optimization Review

    • Review materialized view definitions for optimization opportunities
    • Identify redundant or overlapping materialized views

  3. Documentation and Auditing

    • Document business logic embedded in materialized view definitions
    • Verify materialized view queries match business requirements

  4. Migration Planning

    • Extract materialized view definitions for database migrations
    • Compare materialized view implementations across environments

Script Features

Section titled “Script Features”
  • Flexible Filtering: Supports wildcard patterns for owner and materialized view names
  • Word Wrapping: Long queries are formatted for readability
  • Extended Line Size: 132-character line width accommodates complex queries
  • Long Data Support: Handles large query definitions (up to 2GB)

Commented Features

Section titled “Commented Features”

The script includes commented column definitions for additional materialized view metadata:

  • Refresh mode and method information
  • Rewrite capability settings
  • Last refresh details
  • Updatable status
Section titled “Related Scripts”
  • dtable.sql - Table analysis including materialized view tables
  • dobject.sql - General database object analysis
  • dindex.sql - Index analysis for materialized views

Troubleshooting

Section titled “Troubleshooting”

Query too long to display:

  • Increase set long parameter value for very large queries
  • Use set pagesize 0 to eliminate page breaks in long output

Permission denied:

  • Ensure user has SELECT privilege on DBA_MVIEWS
  • Consider using ALL_MVIEWS for limited access environments

No output returned:

  • Verify materialized view names and ownership
  • Check if materialized views exist in the specified schema
  • Use ’%’ wildcards to broaden search criteria