Materialized View (MVIEW) Not Reflecting Update (Doc ID 1417323.1)

Last updated on FEBRUARY 13, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms

MVIEW doesn't reflect the UPDATES even though "INCLUDING NEW VALUES" is mentioned.
Only the inserted records are being reflected.

-- Create table
SQL> CREATE TABLE ADP_CTM_LOG
2 (
3 LOG_PK NUMBER(10) CONSTRAINT NN_10020_ADP_CTM_LOG NOT NULL,
4 OFFICE_BIC_CODE VARCHAR2(16 BYTE) CONSTRAINT NN_10021_ADP_CTM_LOG NOT NULL,
5 REQUEST_REF_NO VARCHAR2(16 BYTE) CONSTRAINT NN_10022_ADP_CTM_LOG NOT NULL,
6 REQUEST_SENT_DATE DATE CONSTRAINT NN_10023_ADP_CTM_LOG NOT NULL,
7 REQUEST_TYPE VARCHAR2(30 BYTE) CONSTRAINT NN_10024_ADP_CTM_LOG NOT NULL,
8 RESPONSE_MIN_LAST_UPDATE_DATE DATE,
9 RESPONSE_ERROR_CODE VARCHAR2(40 BYTE),
10 APP_REGI_DATE DATE CONSTRAINT NN_10025_ADP_CTM_LOG NOT NULL,
11 APP_UPD_DATE DATE CONSTRAINT NN_10026_ADP_CTM_LOG NOT NULL,
12 CREATED_BY VARCHAR2(20 BYTE) CONSTRAINT NN_10027_ADP_CTM_LOG NOT NULL,
13 CREATION_DATE DATE CONSTRAINT NN_10028_ADP_CTM_LOG NOT NULL,
14 UPDATED_BY VARCHAR2(20 BYTE) CONSTRAINT NN_10029_ADP_CTM_LOG NOT NULL,
15 UPDATE_DATE DATE CONSTRAINT NN_10030_ADP_CTM_LOG NOT NULL
16 )
17 LOGGING
18 NOCOMPRESS
19 NOCACHE
20 NOPARALLEL
21 MONITORING;

Table created.

SQL> ALTER TABLE ADP_CTM_LOG ADD (CONSTRAINT PK_756_ADP_CTM_LOG PRIMARY KEY (LOG_PK));

Table altered.

-- Create MVIEW log
SQL> CREATE MATERIALIZED VIEW LOG ON adp_ctm_log
2 WITH ROWID , SEQUENCE(office_bic_code, response_min_last_update_date), primary key
3 INCLUDING NEW VALUES;

Materialized view log created.

-- Create MVIEW
SQL> CREATE MATERIALIZED VIEW adp_ctm_log_mv
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 AS
5 SELECT office_bic_code, MAX(response_min_last_update_date) response_last_update_date
6 FROM adp_ctm_log
7 GROUP BY office_bic_code;

Materialized view created.

-- Check the date
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
20-02-2012 11:22:08

-- Check the MVIEW last refresh date
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:03

-- Insert rerord into the table
SQL> INSERT INTO ADP_CTM_LOG ( LOG_PK, OFFICE_BIC_CODE, REQUEST_REF_NO, REQUEST_SENT_DATE, REQUEST_TYPE,
2 RESPONSE_MIN_LAST_UPDATE_DATE, RESPONSE_ERROR_CODE, APP_REGI_DATE, APP_UPD_DATE, CREATED_BY,
3 CREATION_DATE, UPDATED_BY, UPDATE_DATE ) VALUES (
4 17, 'TEST1', 'CRQ000000004', TO_Date( '12/28/2011 11:11:12 AM', 'MM/DD/YYYY HH:MI:SS AM')
5 , 'MultiTradeLevelRequest', TO_Date( '01/01/2012 12:07:47 PM', 'MM/DD/YYYY HH:MI:SS AM')
6 , NULL, TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
7 , 'SYSTEM', TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SYSTEM'
8 , TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'));

1 row created.

SQL> COMMIT;

Commit complete.

-- Check the MVIEW log
SQL> select count(*) from mlog$_adp_ctm_log;

COUNT(*)
----------
0

-- Check MVIEW last refresh date. It should be changed, as MVIEW is ON COMMIT refresh. It has actually refreshed.
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:23

SQL> update adp_ctm_log set response_min_last_update_date =sysdate +3 where log_pk=17;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ADP_CTM_LOG_MV;

OFFICE_BIC_CODE RESPONSE_
---------------- ---------
TEST1 01-JAN-12

-- check the last refresh date. It should have changed as the MV is refresh on COMMIT, But it's actually not refreshed.
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:23

SQL>


Why has the MVIEW not refreshed and not reflected the correct entry? To know this, see the next code section.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms