Materialized Views in Oracle Options

codeling 1181 - 5025
@2018-07-31 23:23:47

Refresh Modes for Materialized Views

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some options may not be available. The following table describes the refresh modes.

Refresh Mode Description


Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.


Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

When a materialized view is maintained using the ON COMMIT method, the time required to complete the commit may be slightly longer than usual. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh.

If you think the materialized view did not refresh, check the alert log or trace file.

If a materialized view fails during refresh at COMMIT time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW package after addressing the errors specified in the trace files. Until this is done, the materialized view will no longer be refreshed automatically at commit time.

codeling 1181 - 5025
@2018-07-31 23:25:04

Types of Materialized View Refresh

You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: COMPLETE, FAST, FORCE, and NEVER.

Refresh Option Description


Refreshes by recalculating the materialized view's defining query.


Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.


Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.


Indicates that the materialized view will not be refreshed with refresh mechanisms.

Whether the fast refresh option is available depends upon the type of materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine whether fast refresh is possible.

codeling 1181 - 5025
@2018-08-03 22:02:08

Create Materialized View - Insufficient Privileges

You need to grant CREATE MATERIALIZED VIEW to our schema owner:


codeling 1181 - 5025
@2018-08-03 22:25:40

Dropping a Materialized View

Use the DROP MATERIALIZED VIEW statement to remove an existing materialized view from the database.


The following statement drops the materialized view emp_data in the sample schema hr:


The following statement drops the sales_by_month_by_state materialized view and the underlying table of the materialized view, unless the underlying table was registered in the CREATE MATERIALIZED VIEW statement with the ON PREBUILT TABLE clause:

DROP MATERIALIZED VIEW sales_by_month_by_state;
Users browsing this topic