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')
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
See the output of MV_CAPABILITIES_TABLE which explains why MVIEW w