Resolve Oracle materialized View refresh failure

Possible causes for Oracle materialized view refresh failure:

1. View does not establish materialized view log

2. The base table is authorized to the user

1. Materialized view syntax

Create materialized view [View_name]refresh [Fast|complete|force][on [Commit|demand] |start with (start_time) next (Next _time)]as{Create a materialized view with a query statement} The above is the common syntax for Oracle to create materialized views (materialized view, MV), with the meanings of each parameter as follows: 1.refresh [Fast|complete|force] How the view is refreshed: fast: incremental refresh. Assuming that the previous refresh time is T1, when you refresh the materialized view using fast mode, only the data that has changed from the primary table to the current time period is added to the view T1. In order to record this change, a materialized view Log table is also required to establish an incremental refresh materialized view. Create materialized view Log on (primary table name). (This statement also takes effect when multiple tables are created, and the original table will have two more types of view charts: Mlog$_table_name and Rupd$_table_name) Complete: Refresh all. Equivalent to re-executing the query statement that created the view. Force: This is the default data refresh method. When you can use fast mode, the data refresh will be in fast mode, otherwise use the complete method. 2.MV Data Refresh Time: On Demand: Refresh when the user needs to refresh, here requires the user to refresh the data themselves (can also use the job timed refresh) on commit: When the main table has data submitted, immediately refresh the data in the MV; start ...                                       : Starts at a specified time, and is refreshed once at intervals (specified by next); For example, we're going to flush a full mv_test materialized view: Begin Dbms_mview.refresh (tab=> ' mv_test ', Method=> ' complete ', parallelism=>8), end;/incremental refresh does not need to use what parallel, usually Situation, there is no need for that. Begin Dbms_mview.refresh (tab=> ' mv_test ', method=>' FAST ', parallelism=>1); end;/ Alternatively, this can be done: exec dbms_mview.refresh (' mv_test ', ' F ');

2. Creating materialized view logs for base tables

--TableName for the base table with the back of the primary key, ROWID  primary key is the primary key, ROWID is the table update involves the line number, sequence is sequence pairs, free to add.  --including new values must contain create materialized view Log on TableName with primary key,rowid,sequence (Area_nm_r, Area_nm_n) including new values;

3. Give the primary table permissions to the user who created the view

Grant SELECT on Tabelname to A;

4. Example

--1. Creating materialized view logs for base tables create materialized view Log on Auth_role with ROWID, sequence (role_id, Role_ad, Bpm_group, Role_name, role_e Nable, Role_type, order_num) including new values;--2. Authorize grant Select on Sys_role to Auth;--3. Creating materialized views Create materialized view Viewnamerefresh force on Demandstart with Sysdate next Sysdate + numtodsinterval (2, ' MINUTE ' ) Asselect role_id, Role_ad, Bpm_group, Role_name, role_enable, Role_type, Order_num, ' auth ' sys_code from auth_roleunion Allselect role_id, Role_ad, Bpm_group, Role_name, role_enable, Role_type, Order_num, ' bpm ' Sys_code from Cfcap.sys_roleun Ion Allselect role_id, role_id role_ad, role_id bpm_group, Role_name, To_number (role_stat) Role_enable,to_char (role_ Type) | | ' Role_type, 0 order_num, ' WBS ' sys_code from Forms.ts_role;

　　

　　

Resolve Oracle materialized View refresh failure