Schema

Materialized Views

Create
Display
Drop
Optimize
Manual Refresh

Common Issues

Overview

A Materialized View (MV) contains the results of a query and is physically stored in a database object that resembles a table. A MV can have better performance than selecting the same data-set each time you need it.

MV Creation Fun Facts!

Ensure your TEMP & destination tablespace are large enough.
Otherwise you might see:
ORA-01652: unable to extend temp segment by 8192 in tablespace MY_TS
Ensure the user creating the MV has the required privs:
- ALTER USER scott QUOTA UNLIMITED on HR_TS;
- GRANT SELECT on hr.employee to scott;
- GRANT CREATE MATERIALIZED VIEW TO scott;
  The user whose schema contains the MV must have sufficient quota in the destination tablespace to store the master table and index of the MV or must have the UNLIMITED TABLESPACE system privilege.
The MV can be created from any schema where you have the adequate privs.
If you need an MV Log, it must be created by the owner of the table.
For MV Refreshes, ensure your UNDO tablepace is large enough.
Optionally use: atomic_refresh=>false
Creating a MV using the PARALLEL option decreases the creation time and also tends to use much less TEMP tablespace.
The PARALLEL value used on creation will also be used when the MV is accessed and refreshed.
Do not use "double-quotes" for any column names in your SQL or you will get invalid identifier errors when referring to the column using your MV.
When a MV is created if any of the tables referenced in its definition are changed (via DML or DDL operation) it will cause it to show as INVALID in the dba_objects. This is expected behavior. The MV will still work as normal. See 264036.1.

Using "double-quotes" requires you to refer to that column using the EXACT syntax used on creation limiting your MV's usefulness.

Create

CREATE MATERIALIZED VIEW <MV_Name_Here>
  [BUILD IMMEDIATE|DEFERRED]
  [LOGGING|NOLOGGING]
  [ON DEMAND|ON COMMIT]
  [ON PREBUILT TABLE]
  [PARALLEL n]
  [QUERY REWRITE]
  [REFRESH FAST|COMPLETE|FORCE]
  [TABLESPACE ts_name]
AS <SELECT_SQL_Here>;

 -- Connect to Schema to Create\Own MV
 connect scott/********@MYDB

 -- Confirm Have Access to Table(s) for MV
 SELECT count(*) FROM hr.acctcommon;

 -- Create MV
 CREATE MATERIALIZED VIEW mv_acctcommon
   TABLESPACE hr
   NOLOGGING 
   NOCACHE 
   PARALLEL 8 
   BUILD IMMEDIATE 
   REFRESH FORCE ON DEMAND
 AS 
  SELECT 
     AC.ACCTNBR AcctNbr, 
     AC.TAXRPTFORPERSNBR PersNbr, 
     AC.TAXRPTFORORGNBR OrgNbr, 
     AC.OWNERNAME OwnerName 
  FROM HR.ACCTCOMMON AC 
  ORDER BY AcctNbr; 

 -- Assign Privs to MV As Needed
 GRANT select ON scott.mv_acctcommon TO jsmith;

The above is common for MV that is refreshed manually and does not require an MV Log.
The order of options can be picky. Above order works.

Display

SELECT * FROM all_registered_mviews;
SELECT * FROM all_refresh;
SELECT * FROM all_refresh_children;
SELECT * FROM v$mvrefresh;

COL owner          FORMAT a15
COL name           FORMAT a20
COL mview_site     FORMAT a15
COL can_use_log    FORMAT a3  HEAD 'Log'
COL updatable      FORMAT a3  HEAD 'Upd'
COL refresh_method FORMAT a14
COL mview_id       FORMAT 999999
SELECT mview_site,owner,name,can_use_log,updatable,refresh_method,mview_id, version 
FROM all_registered_mviews
ORDER BY owner,name;

SELECT owner,name,QUERY_TXT
FROM all_registered_mviews
ORDER BY owner,name;

Drop

DROP MATERIALIZED VIEW [schema.]<MVName>;

DROP MATERIALIZED VIEW scott.mv_acctcommon;

All data in the mv table is automatically deleted as part of the dropping process. All indexes and keys for the MV are dropped as well.

Optimize

 ALTER TABLE scott.MyMV ENABLE ROW MOVEMENT;
 ALTER TABLE scott.MyMV SHRINK SPACE COMPACT;
 ALTER TABLE scott.MyMV SHRINK SPACE;
 EXEC DBMS_STATS.gather_table_stats(ownname =>'SCOTT', 
                                    tabname => 'MyMV', 
                                    method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
                                    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
 SELECT count(*) FROM MySchema.MyMV;

You generally want to do this after creating or refreshing a MV.

Manual Refresh

EXECUTE DBMS_MVIEWS.refresh('[schema.]<MVName>','<Type>'[,'atomic_refresh=>true|false']);

EXEC dbms_mview.refresh('scott.mv_acctcommon', method=>'C', atomic_refresh=>false);

Using PARALLEL has no effect on refresh. It will use the PARALLEL value set when you created the MV.
With atomic_refresh=false logs\undo will not be generated and thus faster too.
Legacy method: EXECUTE DBMS_SNAPSHOT.REFRESH('scott.mv_acctcommon','C');

Common Issues

ORA-04021: timeout occurred while waiting to lock object

Solution

 -- Method 1
 1. Look at the DDL for the MVIEW and identify the tables it depends on.

 2. Determine if there is any long running DML occurring on any of the tables 
    the MVIEW depends on.

 3. Retry the drop of the MVIEW when you know there is no long running DML 
    happening on the any of the tables the MVIEW depends on.

 -- Method 2
 1. Use v$access to see if anyone is using the MV (object)?

    SELECT owner, object FROM v$access 
    WHERE owner NOT IN('DBSNMP','GSMADMIN_INTERNAL','PUBLIC','SYS','XDB') 
    ORDER BY owner;

    SELECT object FROM v$access WHERE owner ='SCOTT';

  2. If there are see if they are using summaries.
     SELECT owner,SUMMARY_NAME FROM dba_summaries;

  3. DROP the corresponding summary(s).
     DROP summary SCOTT.MV_MYVIEW;
  4. Try now to DROP your MV.
     DROP MATERIALIZED VIEW SCOTT.MV_MYVIEW;

The above can happen when you try to DROP MATERIALIZED VIEW MyView; .

<- Schema