General
============================
This post describes how to create Materialized View, Materialized Views Group, and monitor Materialized View capabilities with DBMS_MVIEW.explain_mview.
============================
Flow
============================
In Master DB create two tables.
In Master DB create Materialized View Log.
In Remote DB create Materialized View on these two tables.
In Remote DB run DBMS_MVIEW.explain_mview().
In Remote DB run Refresh for Materialized View.
In Remote DB create Refresh Group.
In Remote DB run Refresh for Materialized View Group.
============================
In Master DB
============================
Create two tables, ADDRESS and EMPLOYEE.
CREATE TABLE ADDRESS(
addr_id NUMBER(9) NOT NULL,
addr_street VARCHAR2(100) NOT NULL,
addr_city VARCHAR2(100) NOT NULL,
addr_country VARCHAR2(100) NOT NULL,
addr_zip_code VARCHAR2(100)
);
ALTER TABLE ADDRESS ADD CONSTRAINT ADDRESS_PK PRIMARY KEY (addr_id)
CREATE TABLE EMPLOYEE(
emp_id NUMBER(9) NOT NULL,
emp_first_name VARCHAR2(100) NOT NULL,
emp_last_name VARCHAR2(100) NOT NULL,
emp_address_id NUMBER(9),
emp_birth_date DATE
);
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(emp_id);
============================
In Remote DB
============================
Create Materialized View on these two tables.
============================
Create Materialized View on these two tables.
CREATE MATERIALIZED VIEW ADDRESS_MV REFRESH WITH PRIMARY KEY AS SELECT addr_id, addr_street, addr_city, addr_country, addr_zip_code FROM ADDRESS@ALEC_MASTER_LINK;
CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH WITH PRIMARY KEY AS SELECT emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender FROM EMPLOYEE@ALEC_MASTER_LINK;
Try to refresh EMPLOYEE_MV in Fast mode.
This should not work without first creating Materialized View Log.
This should not work without first creating Materialized View Log.
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
BEGIN DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F'); END;
*
ERROR at line 1:
ORA-23413: table "ALEC_USER"."EMPLOYEE" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
ORA-06512: at line 1
In master DB:
SQL> CREATE MATERIALIZED VIEW LOG ON EMPLOYEE;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW LOG ON ADDRESS;
Materialized view log created.
Refresh
In Remote DB
Start refresh
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
BEGIN DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "ALEC_USER"."EMPLOYEE" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
ORA-06512: at line 1
The solution is to recreate the MATERIALIZED VIEW for EMPLOYEE and ADDRESS.
SQL> DROP MATERIALIZED VIEW EMPLOYEE_MV;
Materialized view dropped.
SQL> CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH WITH PRIMARY KEY
AS SELECT emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date FROM EMPLOYEE@ALEC_MASTER_LINK;
AS SELECT emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date FROM EMPLOYEE@ALEC_MASTER_LINK;
Materialized view created.
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
PL/SQL procedure successfully completed.
SQL> DROP MATERIALIZED VIEW ADDRESS_MV;
Materialized view dropped.
SQL> CREATE MATERIALIZED VIEW ADDRESS_MV REFRESH WITH PRIMARY KEY
AS SELECT addr_id, addr_street, addr_city, addr_country, addr_zip_code FROM ADDRESS@ALEC_MASTER_LINK;
Materialized view created.
SQL> EXEC DBMS_SNAPSHOT.refresh('ADDRESS_MV','F');
PL/SQL procedure successfully completed.
============================
MV_CAPABILITIES_TABLE
============================
MV_CAPABILITIES_TABLE
============================
In Remote DB
Work with table MV_CAPABILITIES_TABLE and DBMS_MVIEW.explain_mview stored Procedure that uses this table
To create table MV_CAPABILITIES_TABLE execute
$ORACLE_HOME/rdbms/admin/utlxmv.sql
$ORACLE_HOME/rdbms/admin/utlxmv.sql
create table MV_CAPABILITIES_TABLE
(
statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number
) ;
STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier
MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
CAPABILITY_NAME VARCHAR(30), -- A descriptive name of the particular capability:
POSSIBLE CHARACTER(1), -- T = capability is possible
F = capability is not possible
RELATED_TEXT VARCHAR(2000),-- Owner.table.column, alias name, etc.
The specific meaning of this column
depends on the MSGNO column.
RELATED_NUM NUMBER, -- A numeric value associated with a row.
The specific meaning of this column depends on
MSGNO column.
MSGNO column.
MSGNO INTEGER, -- When available, QSM message #
Explaining why not possible or more
details when enabled.
MSGTXT VARCHAR(2000),-- Text associated with MSGNO.
SEQ NUMBER);
Once table was created, it is possible to use the Stored Procedure DBMS_MVIEW.explain_mview to analyze Materialized Views.
Need to issue commit, after DBMS_MVIEW.explain_mview()!
EXEC DBMS_MVIEW.explain_mview(mv=>'EMPLOYEE_MV', stmt_id=>'103');
commit;
SELECT STATEMENT_ID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE STATEMENT_ID = 103;
STAT MVOWNER MVNAME CAPABILITY_NAME POSSIBLE RELATED_TEXT MSGTXT
---- ---------- ------------ ----------------------------------- -------- --------------- ------------------
103 ALEC_USER EMPLOYEE_MV PCT N
103 ALEC_USER EMPLOYEE_MV REFRESH_COMPLETE Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST Y
103 ALEC_USER EMPLOYEE_MV REWRITE N
103 ALEC_USER EMPLOYEE_MV PCT_TABLE N EMPLOYEE relation is not a
partitioned table
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_INSERT Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_ONETAB_DML Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_ANY_DML Y
Once table was created, it is possible to use the Stored Procedure DBMS_MVIEW.explain_mview to analyze Materialized Views.
Need to issue commit, after DBMS_MVIEW.explain_mview()!
EXEC DBMS_MVIEW.explain_mview(mv=>'EMPLOYEE_MV', stmt_id=>'103');
commit;
SELECT STATEMENT_ID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE STATEMENT_ID = 103;
STAT MVOWNER MVNAME CAPABILITY_NAME POSSIBLE RELATED_TEXT MSGTXT
---- ---------- ------------ ----------------------------------- -------- --------------- ------------------
103 ALEC_USER EMPLOYEE_MV PCT N
103 ALEC_USER EMPLOYEE_MV REFRESH_COMPLETE Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST Y
103 ALEC_USER EMPLOYEE_MV REWRITE N
103 ALEC_USER EMPLOYEE_MV PCT_TABLE N EMPLOYEE relation is not a
partitioned table
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_INSERT Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_ONETAB_DML Y
103 ALEC_USER EMPLOYEE_MV REFRESH_FAST_AFTER_ANY_DML Y
Per the MV_CAPABILITIES_TABLE - both Fast Refresh and Complete Refresh as possible for Materialized View EMPLOYEE_MV
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','C');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
PL/SQL procedure successfully completed.
The same configuration is also done for Materialized View ADDRESS_MV.
============================
Example of Materialized view using REFRESH on ROWID
============================
The default of Materialized View refresh is WITH PRIMARY KEY.
It is possible to override this default behavior by explicitly stating WITH ROW ID.
Example of Materialized view using REFRESH on ROWID
============================
The default of Materialized View refresh is WITH PRIMARY KEY.
It is possible to override this default behavior by explicitly stating WITH ROW ID.
CONTACT Materialized View is based on ROWID
SQL> CREATE TABLE CONTACT(
2 cnt_id NUMBER(9) NOT NULL,
3 cnt_name VARCHAR2(100) NOT NULL,
4 cnt_addr_id NUMBER(9) NOT NULL,
5 cnt_company VARCHAR2(100),
6 cnt_dept VARCHAR2(100),
7 cnt_position VARCHAR2(100)
8 );
Table created.
SQL> ALTER TABLE CONTACT ADD CONSTRAINT CONTACT_PK PRIMARY KEY (cnt_id);
Table altered.
CREATE MATERIALIZED VIEW LOG ON CONTACT;
The table MLOG$_XXX is created when CREATE MATERIALIZED VIEW LOG ON XXX is executed.
SELECT table_name FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';
SELECT table_name FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';
TABLE_NAME
CREATE MATERIALIZED VIEW LOG ON CONTACT;
------------------------------
MLOG$_ADDRESS
MLOG$_CONTACT
MLOG$_EMPLOYEE
SQL> DROP TABLE CONTACT;
Table dropped.
SQL> SELECT table_name FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';
TABLE_NAME
------------------------------
MLOG$_ADDRESS
MLOG$_EMPLOYEE
The MLOG$_CONTACT was deleted automatically where the master table was dropped.
Now - create again.
SQL> CREATE TABLE CONTACT(
2 cnt_id NUMBER(9) NOT NULL,
3 cnt_name VARCHAR2(100) NOT NULL,
4 cnt_addr_id NUMBER(9) NOT NULL,
5 cnt_company VARCHAR2(100),
6 cnt_dept VARCHAR2(100),
7 cnt_position VARCHAR2(100)
8 );
Table created.
SQL> ALTER TABLE CONTACT ADD CONSTRAINT CONTACT_PK PRIMARY KEY (cnt_id);
Table altered.
CREATE MATERIALIZED VIEW LOG ON CONTACT;
In Remote DB
SQL> CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
2 SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
3 FROM CONTACT@ALEC_MASTER_LINK;
Materialized view created.
SQL> EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'111');
PL/SQL procedure successfully completed.
commit;
SQL> SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG
2* FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 111
SID MVOWNER MVNAME CAPABILITY_NAME POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
111 ALEC_USER CONTACT_MV PCT N -
111 ALEC_USER CONTACT_MV REFRESH_COMPLETE Y -
111 ALEC_USER CONTACT_MV REFRESH_FAST N -
111 ALEC_USER CONTACT_MV REWRITE N -
111 ALEC_USER CONTACT_MV PCT_TABLE N CONTACT-relation is not a partitioned
table
table
111 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_INSERT N ALEC_USER.CONTACT-mv log must have ROWID
111 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML N -see the reason why
REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_INSERT is disabled
111 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ANY_DML N -see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_ONETAB_DML is disabled
111 ALEC_USER CONTACT_MV REFRESH_FAST_PCT N -PCT is not possible on any of the detail
tables in the materialized view
tables in the materialized view
111 ALEC_USER CONTACT_MV REWRITE_FULL_TEXT_MATCH N CONTACT-mv references a remote table
orview in the FROM list
orview in the FROM list
111 ALEC_USER CONTACT_MV REWRITE_FULL_TEXT_MATCH N -query rewrite is disabled on the
materialized view
materialized view
111 ALEC_USER CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH N -materialized view cannot support any type
of query rewrite
of query rewrite
111 ALEC_USER CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH N -query rewrite is disabled on the
materialized view
materialized view
111 ALEC_USER CONTACT_MV REWRITE_GENERAL N -materialized view cannot support any type
of query rewrite
of query rewrite
111 ALEC_USER CONTACT_MV REWRITE_GENERAL N -query rewrite is disabled on the
materialized view
materialized view
111 ALEC_USER CONTACT_MV REWRITE_PCT N -general rewrite is not possible or PCT is
not possible on any of the detail tables
not possible on any of the detail tables
111 ALEC_USER CONTACT_MV PCT_TABLE_REWRITE N CONTACT-relation is not a partitioned
table
table
Without explicitly including the ROWID in the selected fields the Fast refresh is not possible.
DROP MATERIALIZED VIEW CONTACT_MV;
SQL> CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
2 SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
3 FROM CONTACT@ALEC_MASTER_LINK;
Materialized view created.
EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'112');
commit;
SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 112;
SQL> SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG
2* FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 112
SID MVOWNER MVNAME CAPABILITY_NAME POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
112 ALEC_USER CONTACT_MV PCT N -
112 ALEC_USER CONTACT_MV REFRESH_COMPLETE Y -
112 ALEC_USER CONTACT_MV REFRESH_FAST N -
112 ALEC_USER CONTACT_MV REWRITE N -
112 ALEC_USER CONTACT_MV PCT_TABLE N CONTACT-relation is not a partitioned
table
table
112 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_INSERT N ALEC_USER.CONTACT-mv log must have ROWID
112 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML N -see the reason why
REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_INSERT is disabled
112 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ANY_DML N -see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_ONETAB_DML is disabled
112 ALEC_USER CONTACT_MV REFRESH_FAST_PCT N -PCT is not possible on any of the detail
tables in the materialized view
tables in the materialized view
112 ALEC_USER CONTACT_MV REWRITE_FULL_TEXT_MATCH N CONTACT-mv references a remote table
orview in the FROM list
orview in the FROM list
112 ALEC_USER CONTACT_MV REWRITE_FULL_TEXT_MATCH N -query rewrite is disabled on the
materialized view
materialized view
112 ALEC_USER CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH N -materialized view cannot support any type
of query rewrite
of query rewrite
112 ALEC_USER CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH N -query rewrite is disabled on the
materialized view
materialized view
112 ALEC_USER CONTACT_MV REWRITE_GENERAL N -materialized view cannot support any type
of query rewrite
of query rewrite
112 ALEC_USER CONTACT_MV REWRITE_GENERAL N -query rewrite is disabled on the
materialized view
materialized view
112 ALEC_USER CONTACT_MV REWRITE_PCT N -general rewrite is not possible or PCT is
not possible on any of the detail tables
not possible on any of the detail tables
112 ALEC_USER CONTACT_MV PCT_TABLE_REWRITE N CONTACT-relation is not a partitioned
table
table
Still not good...
Need to fix on the master side - fix the ALEC_USER.CONTACT-mv log - it "must have ROWID" and recreate the Materialized View.
The CREATE MATERIALIZED VIEW on Remote DB should be in sync with CREATE MATERIALIZED VIEW LOG on Master DB. Both should have same WITH clause. In this case, it should be WITH ROWID.
The CREATE MATERIALIZED VIEW on Remote DB should be in sync with CREATE MATERIALIZED VIEW LOG on Master DB. Both should have same WITH clause. In this case, it should be WITH ROWID.
SQL> DROP MATERIALIZED VIEW LOG ON CONTACT;
Materialized view log dropped.
SQL> CREATE MATERIALIZED VIEW LOG ON CONTACT WITH ROWID;
Materialized view log created.
DROP MATERIALIZED VIEW CONTACT_MV;
CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
FROM CONTACT@ALEC_MASTER_LINK;
Materialized view created.
EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'114');
commit;
SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 114
SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 114
SID MVOWNER MVNAME CAPABILITY_NAME POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
114 ALEC_USER CONTACT_MV PCT N -
114 ALEC_USER CONTACT_MV REFRESH_COMPLETE Y -
114 ALEC_USER CONTACT_MV REFRESH_FAST Y -
114 ALEC_USER CONTACT_MV REWRITE N -
114 ALEC_USER CONTACT_MV PCT_TABLE N CONTACT-relation is not a partitioned table
114 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_INSERT Y -
114 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML Y -
114 ALEC_USER CONTACT_MV REFRESH_FAST_AFTER_ANY_DML Y -
Now CONTACT_MV can be Fast Refreshed.
MLOG$_<TABLE_NAME>
Lets check the structure of MLOG$_XXX tables.
SQL> DESC MLOG$_ADDRESS
Name Type
---------------- -----------------
ADDR_ID NUMBER(9)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
SQL> DESC MLOG$_EMPLOYEE
Name Type
---------------- ------------------
EMP_ID NUMBER(9)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
SQL> DESC MLOG$_CONTACT
Name Type
---------------- ------------------
M_ROW$$ VARCHAR2(255 CHAR)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
Create Refresh Group.
Refresh Group is useful when there is a need to refresh several Materialized Views in a synchronized way.
Step A. - Create the Materialized Group BEGIN
DBMS_REFRESH.MAKE (
name => 'ALEC_USER.MV_GROUP_EMP_INFO',
list => '',
next_date => TRUNC(SYSDATE+1),
interval => 'TRUNC(SYSDATE + 1) +1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE
);
commit;
END;
/
Note:
next_date - is DATE
interval - is VARCHAR2
Step B. - Add Materialized Views to the Materialized Group BEGIN
DBMS_REFRESH.ADD (
name => 'MV_GROUP_EMP_INFO',
list => 'EMPLOYEE_MV',
lax => TRUE);
commit;
END;
/
BEGIN
DBMS_REFRESH.ADD (
name => 'MV_GROUP_EMP_INFO',
list => 'ADDRESS_MV',
lax => TRUE);
commit;
END;
/
Refresh command
BEGIN
DBMS_REFRESH.REFRESH ('MV_GROUP_EMP_INFO');
END;
/
is equivalent to running:
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
DBMS_SNAPSHOT.REFRESH('ADDRESS_MV','F');
END;
/
Both EMPLOYEE_MV and ADDRESS_MV where refreshed.
============================
REFRESH GROUP TABLES
============================
The two table to look at are:
USER_REFRESH
USER_REFRESH_CHILDREN
SQL> DESC USER_REFRESH
Name Type
-------------------- -----------------
ROWNER VARCHAR2(30)
RNAME VARCHAR2(30)
REFGROUP NUMBER
IMPLICIT_DESTROY VARCHAR2(1 CHAR)
PUSH_DEFERRED_RPC VARCHAR2(1 CHAR)
REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
ROLLBACK_SEG VARCHAR2(30)
JOB NUMBER
NEXT_DATE DATE
INTERVAL VARCHAR2(200)
BROKEN VARCHAR2(1 CHAR)
PURGE_OPTION NUMBER(38)
PARALLELISM NUMBER(38)
HEAP_SIZE NUMBER(38)
SQL> DESC USER_REFRESH_CHILDREN
Name Type
-------------------- -----------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(30)
ROWNER VARCHAR2(30)
RNAME VARCHAR2(30)
REFGROUP NUMBER
IMPLICIT_DESTROY VARCHAR2(1 CHAR)
PUSH_DEFERRED_RPC VARCHAR2(1 CHAR)
REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
ROLLBACK_SEG VARCHAR2(30)
JOB NUMBER
NEXT_DATE DATE
INTERVAL VARCHAR2(200)
BROKEN VARCHAR2(1 CHAR)
PURGE_OPTION NUMBER(38)
PARALLELISM NUMBER(38)
HEAP_SIZE NUMBER(38)
USER_REFRESH
SELECT ROWNER||'.'||RNAME AS REF_GROUP, REFGROUP, NEXT_DATE, INTERVAL FROM USER_REFRESH;
REF_GROUP REFGROUP NEXT_DATE INTERVAL
------------------------------ ---------- --------- ------------------------------
ALEC_USER.MV_GROUP_EMP_INFO 101 24-SEP-14 TRUNC(SYSDATE + 1) +1/24
USER_REFRESH_CHILDREN
SELECT OWNER||'.'||NAME AS MVIEW_NAME, TYPE, ROWNER||'.'||RNAME AS REFRESH_GROUP_NAME, REFGROUP AS
FROM USER_REFRESH_CHILDREN;
MVIEW_NAME TYPE REFRESH_GROUP_NAME REFGROUP
------------------------- ---------- ------------------------------ ----------
ALEC_USER.EMPLOYEE_MV SNAPSHOT ALEC_USER.MV_GROUP_EMP_INFO 101
ALEC_USER.ADDRESS_MV SNAPSHOT ALEC_USER.MV_GROUP_EMP_INFO 101
Refresh Materialized View, an example
On Master DB:
SQL> SELECT * FROM EMPLOYEE;
no rows selected
SELECT * FROM MLOG$_EMPLOYEE
no rows selected
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1001,'Hetzel','Hertzel', 1, TO_DATE('18840524','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1002,'Haim Nachman','Bialik', 2, TO_DATE('18891124','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1003,'David','Ben Gurion', 3, TO_DATE('18740121','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1004,'Haim','Arlozorov', 4, TO_DATE('19000112','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1005,'Sami','Samsonov', 5, TO_DATE('20140507','YYYYMMDD'),'M');
SQL> SELECT * FROM EMPLOYEE;
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------ -------------------- -------------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Sami Samsonov 5 07-MAY-14 M
SQL> SELECT * FROM MLOG$_EMPLOYEE
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1001 01-JAN-00 I N FE
1002 01-JAN-00 I N FE
1003 01-JAN-00 I N FE
1004 01-JAN-00 I N FE
1005 01-JAN-00 I N FE
on Remote DB:
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
END;
/
On Master DB:
SELECT * FROM MLOG$_EMPLOYEE
no rows selected
When all the depending Materialized Views have refreshed data - this data is deleted from MLOG$_XXX table.
SQL> UPDATE EMPLOYEE SET emp_first_name = 'Nachum' WHERE emp_id = 1005;
1 row updated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
SQL> commit;
Commit complete.
The entry is created in table MLOG$_EMPLOYEE before the transaction commits.
SQL> UPDATE EMPLOYEE SET emp_first_name = 'Shlomo' WHERE emp_id = 1005;
1 row updated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
1005 01-JAN-00 U U 04
SQL> rollback;
Rollback complete.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
How does Materialized View Refresh coup with TRUNCATE?
On Master DB:
SQL> TRUNCATE TABLE EMPLOYEE;
Table truncated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
no rows selected
SQL> SELECT * FROM EMPLOYEE;
no rows selected
On Remote DB:
SELECT * FROM EMPLOYEE_MV
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Nachum Samsonov 5 07-MAY-14 M
What would happen after Fast refresh?
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
END;
/
PL/SQL procedure successfully completed.
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Nachum Samsonov 5 07-MAY-14 M
Now we are out of sync!!!
The table on Master DB was Truncated, but this change is not logged to MLOG$_EMPLOYEE and is not propagated to the remote DB.
Need a COMPLETE Refresh to force refresh in the Remote DB.
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','C');
END;
/
SQL> SELECT * FROM EMPLOYEE_MV;
no rows selected
Now we are back in sync.
How to track refresh events
Check tables:
- USER_MVIEWS
- USER_MVIEW_REFRESH_TIMES
- USER_MVIEW_ANALYSIS
USER_MVIEWS
SQL> DESC USER_MVIEWS;
Name Type
---------------------- --------------------
OWNER VARCHAR2(30)
MVIEW_NAME VARCHAR2(30)
CONTAINER_NAME VARCHAR2(30)
QUERY LONG
QUERY_LEN NUMBER(38)
UPDATABLE VARCHAR2(1 CHAR)
UPDATE_LOG VARCHAR2(30)
MASTER_ROLLBACK_SEG VARCHAR2(30)
MASTER_LINK VARCHAR2(128)
REWRITE_ENABLED VARCHAR2(1 CHAR)
REWRITE_CAPABILITY VARCHAR2(9 CHAR)
REFRESH_MODE VARCHAR2(6 CHAR)
REFRESH_METHOD VARCHAR2(8 CHAR)
BUILD_MODE VARCHAR2(9 CHAR)
FAST_REFRESHABLE VARCHAR2(18 CHAR)
LAST_REFRESH_TYPE VARCHAR2(8 CHAR)
LAST_REFRESH_DATE DATE
STALENESS VARCHAR2(19 CHAR)
AFTER_FAST_REFRESH VARCHAR2(19 CHAR)
UNKNOWN_PREBUILT VARCHAR2(1 CHAR)
UNKNOWN_PLSQL_FUNC VARCHAR2(1 CHAR)
UNKNOWN_EXTERNAL_TABLE VARCHAR2(1 CHAR)
UNKNOWN_CONSIDER_FRESH VARCHAR2(1 CHAR)
UNKNOWN_IMPORT VARCHAR2(1 CHAR)
UNKNOWN_TRUSTED_FD VARCHAR2(1 CHAR)
COMPILE_STATE VARCHAR2(19 CHAR)
USE_NO_INDEX VARCHAR2(1 CHAR)
STALE_SINCE DATE
SELECT OWNER, MVIEW_NAME, CONTAINER_NAME, UPDATABLE, UPDATE_LOG, REFRESH_MODE, REFRESH_METHOD, LAST_REFRESH_TYPE, LAST_REFRESH_DATE AS LAST_DATE
FROM USER_MVIEWS
OWNER MVIEW_NAME CONTAINER_NAME U UPDATE_LOG REFRESH_MODE REFRESH_METHOD LAST_REFRESH_TYPE LAST_DATE
---------- ------------ -------------- - ---------- ------------ -------------- ----------------- ----------
ALEC_USER CONTACT_MV CONTACT_MV N DEMAND FORCE COMPLETE 22-SEP-14
ALEC_USER EMP_ADDR_MV EMP_ADDR_MV N DEMAND FORCE COMPLETE 17-SEP-14
ALEC_USER EMPLOYEE_MV EMPLOYEE_MV N DEMAND FORCE COMPLETE 23-SEP-14
ALEC_USER ADDRESS_MV ADDRESS_MV N DEMAND FORCE FAST 23-SEP-14
USER_MVIEW_REFRESH_TIMES
DESC USER_MVIEW_REFRESH_TIMES
SQL> DESC USER_MVIEW_REFRESH_TIMES
Name Type
------------ -------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
MASTER_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
LAST_REFRESH DATE
SELECT OWNER||'.'||NAME AS MVIEW_NAME, MASTER_OWNER||'.'||MASTER AS MASTER_TABLE, LAST_REFRESH
FROM USER_MVIEW_REFRESH_TIMES
ORDER BY LAST_REFRESH DESC;
MVIEW_NAME MASTER_TABLE LAST_REFRESH
--------------------- ------------------ ------------
ALEC_USER.EMPLOYEE_MV ALEC_USER.EMPLOYEE 23-SEP-14
ALEC_USER.ADDRESS_MV ALEC_USER.ADDRESS 23-SEP-14
ALEC_USER.CONTACT_MV ALEC_USER.CONTACT 22-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.ADDRESS 17-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.EMPLOYEE 17-SEP-14
USER_MVIEW_ANALYSIS
DESC USER_MVIEW_ANALYSIS
SQL> DESC USER_MVIEW_ANALYSIS
Name Type
----------------- -----------------
MVIEW_NAME VARCHAR2(30)
MVIEW_TABLE_OWNER VARCHAR2(30)
CONTAINER_NAME VARCHAR2(30)
LAST_REFRESH_SCN NUMBER
LAST_REFRESH_DATE DATE
REFRESH_METHOD VARCHAR2(8 CHAR)
SUMMARY VARCHAR2(1 CHAR)
FULLREFRESHTIM NUMBER
INCREFRESHTIM NUMBER
CONTAINS_VIEWS VARCHAR2(1 CHAR)
UNUSABLE VARCHAR2(1 CHAR)
RESTRICTED_SYNTAX VARCHAR2(1 CHAR)
INC_REFRESHABLE VARCHAR2(1 CHAR)
KNOWN_STALE VARCHAR2(1 CHAR)
INVALID VARCHAR2(1 CHAR)
REWRITE_ENABLED VARCHAR2(1 CHAR)
QUERY_LEN NUMBER
QUERY LONG
REVISION NUMBER
SELECT MVIEW_NAME AS MVIEW_NAME, MVIEW_TABLE_OWNER TABLE_OWNER, CONTAINER_NAME, LAST_REFRESH_DATE AS LAST_REFRESH, REFRESH_METHOD AS METHOD,
fullrefreshtim AS FULL_REFRESH_TIME,
increfreshtim AS INCRE_REFRESH_TIME
FROM USER_MVIEW_ANALYSIS;
MVIEW_NAME TABLE_OWNER CONTAINER_NAME LAST_REFRESH METHOD FULL_REFRESH_TIME INCRE_REFRESH_TIME
--------------------- ----------- -------------- ------------ -------- ----------------- -------------------
CONTACT_MV ALEC_USER CONTACT_MV 22-SEP-14 FORCE 0 0
EMP_ADDR_MV ALEC_USER EMP_ADDR_MV 17-SEP-14 FORCE 0 0
EMPLOYEE_MV ALEC_USER EMPLOYEE_MV 23-SEP-14 FORCE 0 0
ADDRESS_MV ALEC_USER ADDRESS_MV 23-SEP-14 FORCE 0 0
To display the start time and end time, use below calculation:
SELECT
MVIEW_NAME AS MVIEW_NAME,
TO_CHAR(last_refresh_date,'YYYY-MM-DD hh24:mm:ss') "START_TIME",
TO_CHAR(CASE
WHEN fullrefreshtim <> 0 THEN
LAST_REFRESH_DATE + fullrefreshtim/60/60/24
WHEN increfreshtim <> 0 THEN
LAST_REFRESH_DATE + increfreshtim/60/60/24
ELSE
LAST_REFRESH_DATE
END,'YYYY-MM-DD hh24:mm:ss') "END_TIME",
fullrefreshtim AS FULL_REFRESH_TIME,
increfreshtim AS INCREMENT_REFRESH_TIME
FROM USER_MVIEW_ANALYSIS;
MVIEW_NAME START_TIME END_TIME FULL_REFRESH_TIME INCREMENT_REFRESH_TIME
--------------------- -------------------- ------------------- ----------------- ----------------------
CONTACT_MV 2014-09-22 13:09:55 2014-09-22 13:09:55 0 0
EMP_ADDR_MV 2014-09-17 05:09:16 2014-09-17 05:09:16 0 0
EMPLOYEE_MV 2014-09-23 09:09:39 2014-09-23 09:09:39 0 0
ADDRESS_MV 2014-09-23 08:09:25 2014-09-23 08:09:25 0 0
Appendix
Useful Reference Sites
http://www.bluegecko.net/oracle/implementing-count-distinct-with-fast-refresh-materialized-views/
CREATE MATERIALIZED VIEW LOG options
When running command "CREATE MATERIALIZED VIEW LOG ON XXX" There are few options:
WITH PRIMARY KEY - This is the default.
Indicates that Primary Key of all changed rows should be
recorded in the materialized view log.
WITH ROWID - Indicates that rowid of all changed rows should be
recorded in the materialized view log.
WITH OBJECT ID - Only for object table, not for storage tables.
WITH SEQUENCE - Additional information, to provide ordering information to be
recorded in the materialized view log.
Combination: The Materialized View Log records changes to the master table or master materialized view based any combination of primary key, ROWID, and object identifier options.
============================
Materialized Log info
============================
Materialized Log info is stored in USER_MVIEW_LOGS.
USER_MVIEW_LOGS
SELECT LOG_OWNER, MASTER, LOG_TABLE, ROWIDS, PRIMARY_KEY OBJECT_ID FROM USER_MVIEW_LOGS;
LOG_OWNER MASTER LOG_TABLE ROWIDS OBJECT_ID
--------------- --------------- --------------- ------ ---------
ALEC_USER ADDRESS MLOG$_ADDRESS NO YES
ALEC_USER CONTACT MLOG$_CONTACT YES NO
ALEC_USER EMPLOYEE MLOG$_EMPLOYEE NO YES
WITH PRIMARY KEY - This is the default.
Indicates that Primary Key of all changed rows should be
recorded in the materialized view log.
WITH ROWID - Indicates that rowid of all changed rows should be
recorded in the materialized view log.
WITH OBJECT ID - Only for object table, not for storage tables.
WITH SEQUENCE - Additional information, to provide ordering information to be
recorded in the materialized view log.
============================
Difference between WITH PRIMARY KEY and WITH ROWID
============================
Implementation:
WITH PRIMARY KEY - records affected rows using the primary key
WITH ROWID - records affected rows using the physical rowid
Practical Impact:
Using WITH ROWID makes reorganizing and truncating your master tables difficult because it prevents your
Use:
The WITH PRIMARY KEY option is the preferred option, unless Primary Key is not available.
============================
Implementation:
WITH PRIMARY KEY - records affected rows using the primary key
WITH ROWID - records affected rows using the physical rowid
Practical Impact:
Using WITH ROWID makes reorganizing and truncating your master tables difficult because it prevents your
ROWID materialized views from being fast refreshed. If you reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed.Use:
The WITH PRIMARY KEY option is the preferred option, unless Primary Key is not available.
============================
============================
Materialized Log info is stored in USER_MVIEW_LOGS.
USER_MVIEW_LOGS
SELECT LOG_OWNER, MASTER, LOG_TABLE, ROWIDS, PRIMARY_KEY OBJECT_ID FROM USER_MVIEW_LOGS;
LOG_OWNER MASTER LOG_TABLE ROWIDS OBJECT_ID
--------------- --------------- --------------- ------ ---------
ALEC_USER ADDRESS MLOG$_ADDRESS NO YES
ALEC_USER CONTACT MLOG$_CONTACT YES NO
ALEC_USER EMPLOYEE MLOG$_EMPLOYEE NO YES
Lets check the structure of MLOG$_XXX tables.
Name Type
---------------- -----------------
ADDR_ID NUMBER(9)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
SQL> DESC MLOG$_EMPLOYEE
Name Type
---------------- ------------------
EMP_ID NUMBER(9)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
SQL> DESC MLOG$_CONTACT
Name Type
---------------- ------------------
M_ROW$$ VARCHAR2(255 CHAR)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1 CHAR)
OLD_NEW$$ VARCHAR2(1 CHAR)
CHANGE_VECTOR$$ RAW(255)
The MATERIALIZED VIEW LOG ON CONTACT was created with "WITH ROWID" clause, rather than the default "WITH PRIMARY KEY".
The rowid of the new/modified row is stored in materialized view log M_ROW$$ column.
Create Refresh Group.
Refresh Group is useful when there is a need to refresh several Materialized Views in a synchronized way.
Step A. - Create the Materialized Group BEGIN
DBMS_REFRESH.MAKE (
name => 'ALEC_USER.MV_GROUP_EMP_INFO',
list => '',
next_date => TRUNC(SYSDATE+1),
interval => 'TRUNC(SYSDATE + 1) +1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE
);
commit;
END;
/
Note:
next_date - is DATE
interval - is VARCHAR2
Step B. - Add Materialized Views to the Materialized Group BEGIN
DBMS_REFRESH.ADD (
name => 'MV_GROUP_EMP_INFO',
list => 'EMPLOYEE_MV',
lax => TRUE);
commit;
END;
/
BEGIN
DBMS_REFRESH.ADD (
name => 'MV_GROUP_EMP_INFO',
list => 'ADDRESS_MV',
lax => TRUE);
commit;
END;
/
Refresh command
BEGIN
DBMS_REFRESH.REFRESH ('MV_GROUP_EMP_INFO');
END;
/
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
DBMS_SNAPSHOT.REFRESH('ADDRESS_MV','F');
END;
/
Both EMPLOYEE_MV and ADDRESS_MV where refreshed.
============================
REFRESH GROUP TABLES
============================
The two table to look at are:
USER_REFRESH
USER_REFRESH_CHILDREN
SQL> DESC USER_REFRESH
Name Type
-------------------- -----------------
ROWNER VARCHAR2(30)
RNAME VARCHAR2(30)
REFGROUP NUMBER
IMPLICIT_DESTROY VARCHAR2(1 CHAR)
PUSH_DEFERRED_RPC VARCHAR2(1 CHAR)
REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
ROLLBACK_SEG VARCHAR2(30)
JOB NUMBER
NEXT_DATE DATE
INTERVAL VARCHAR2(200)
BROKEN VARCHAR2(1 CHAR)
PURGE_OPTION NUMBER(38)
PARALLELISM NUMBER(38)
HEAP_SIZE NUMBER(38)
SQL> DESC USER_REFRESH_CHILDREN
Name Type
-------------------- -----------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(30)
ROWNER VARCHAR2(30)
RNAME VARCHAR2(30)
REFGROUP NUMBER
IMPLICIT_DESTROY VARCHAR2(1 CHAR)
PUSH_DEFERRED_RPC VARCHAR2(1 CHAR)
REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
ROLLBACK_SEG VARCHAR2(30)
JOB NUMBER
NEXT_DATE DATE
INTERVAL VARCHAR2(200)
BROKEN VARCHAR2(1 CHAR)
PURGE_OPTION NUMBER(38)
PARALLELISM NUMBER(38)
HEAP_SIZE NUMBER(38)
USER_REFRESH
SELECT ROWNER||'.'||RNAME AS REF_GROUP, REFGROUP, NEXT_DATE, INTERVAL FROM USER_REFRESH;
REF_GROUP REFGROUP NEXT_DATE INTERVAL
------------------------------ ---------- --------- ------------------------------
ALEC_USER.MV_GROUP_EMP_INFO 101 24-SEP-14 TRUNC(SYSDATE + 1) +1/24
USER_REFRESH_CHILDREN
SELECT OWNER||'.'||NAME AS MVIEW_NAME, TYPE, ROWNER||'.'||RNAME AS REFRESH_GROUP_NAME, REFGROUP AS
FROM USER_REFRESH_CHILDREN;
MVIEW_NAME TYPE REFRESH_GROUP_NAME REFGROUP
------------------------- ---------- ------------------------------ ----------
ALEC_USER.EMPLOYEE_MV SNAPSHOT ALEC_USER.MV_GROUP_EMP_INFO 101
ALEC_USER.ADDRESS_MV SNAPSHOT ALEC_USER.MV_GROUP_EMP_INFO 101
Refresh Materialized View, an example
On Master DB:
SQL> SELECT * FROM EMPLOYEE;
no rows selected
SELECT * FROM MLOG$_EMPLOYEE
no rows selected
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1001,'Hetzel','Hertzel', 1, TO_DATE('18840524','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1002,'Haim Nachman','Bialik', 2, TO_DATE('18891124','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1003,'David','Ben Gurion', 3, TO_DATE('18740121','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1004,'Haim','Arlozorov', 4, TO_DATE('19000112','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1005,'Sami','Samsonov', 5, TO_DATE('20140507','YYYYMMDD'),'M');
SQL> SELECT * FROM EMPLOYEE;
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------ -------------------- -------------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Sami Samsonov 5 07-MAY-14 M
SQL> SELECT * FROM MLOG$_EMPLOYEE
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1001 01-JAN-00 I N FE
1002 01-JAN-00 I N FE
1003 01-JAN-00 I N FE
1004 01-JAN-00 I N FE
1005 01-JAN-00 I N FE
on Remote DB:
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
END;
/
On Master DB:
SELECT * FROM MLOG$_EMPLOYEE
no rows selected
When all the depending Materialized Views have refreshed data - this data is deleted from MLOG$_XXX table.
SQL> UPDATE EMPLOYEE SET emp_first_name = 'Nachum' WHERE emp_id = 1005;
1 row updated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
SQL> commit;
Commit complete.
The entry is created in table MLOG$_EMPLOYEE before the transaction commits.
SQL> UPDATE EMPLOYEE SET emp_first_name = 'Shlomo' WHERE emp_id = 1005;
1 row updated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
1005 01-JAN-00 U U 04
SQL> rollback;
Rollback complete.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
1005 01-JAN-00 U U 04
How does Materialized View Refresh coup with TRUNCATE?
On Master DB:
SQL> TRUNCATE TABLE EMPLOYEE;
Table truncated.
SQL> SELECT * FROM MLOG$_EMPLOYEE;
no rows selected
SQL> SELECT * FROM EMPLOYEE;
no rows selected
On Remote DB:
SELECT * FROM EMPLOYEE_MV
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Nachum Samsonov 5 07-MAY-14 M
What would happen after Fast refresh?
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
END;
/
PL/SQL procedure successfully completed.
EMP_ID EMP_FIRST_NAME EMP_LAST_NAME EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
1001 Hetzel Hertzel 1 24-MAY-84 M
1002 Haim Nachman Bialik 2 24-NOV-89 M
1003 David Ben Gurion 3 21-JAN-74 M
1004 Haim Arlozorov 4 12-JAN-00 M
1005 Nachum Samsonov 5 07-MAY-14 M
Now we are out of sync!!!
The table on Master DB was Truncated, but this change is not logged to MLOG$_EMPLOYEE and is not propagated to the remote DB.
Need a COMPLETE Refresh to force refresh in the Remote DB.
BEGIN
DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','C');
END;
/
SQL> SELECT * FROM EMPLOYEE_MV;
no rows selected
Now we are back in sync.
How to track refresh events
Check tables:
- USER_MVIEWS
- USER_MVIEW_REFRESH_TIMES
- USER_MVIEW_ANALYSIS
USER_MVIEWS
SQL> DESC USER_MVIEWS;
Name Type
---------------------- --------------------
OWNER VARCHAR2(30)
MVIEW_NAME VARCHAR2(30)
CONTAINER_NAME VARCHAR2(30)
QUERY LONG
QUERY_LEN NUMBER(38)
UPDATABLE VARCHAR2(1 CHAR)
UPDATE_LOG VARCHAR2(30)
MASTER_ROLLBACK_SEG VARCHAR2(30)
MASTER_LINK VARCHAR2(128)
REWRITE_ENABLED VARCHAR2(1 CHAR)
REWRITE_CAPABILITY VARCHAR2(9 CHAR)
REFRESH_MODE VARCHAR2(6 CHAR)
REFRESH_METHOD VARCHAR2(8 CHAR)
BUILD_MODE VARCHAR2(9 CHAR)
FAST_REFRESHABLE VARCHAR2(18 CHAR)
LAST_REFRESH_TYPE VARCHAR2(8 CHAR)
LAST_REFRESH_DATE DATE
STALENESS VARCHAR2(19 CHAR)
AFTER_FAST_REFRESH VARCHAR2(19 CHAR)
UNKNOWN_PREBUILT VARCHAR2(1 CHAR)
UNKNOWN_PLSQL_FUNC VARCHAR2(1 CHAR)
UNKNOWN_EXTERNAL_TABLE VARCHAR2(1 CHAR)
UNKNOWN_CONSIDER_FRESH VARCHAR2(1 CHAR)
UNKNOWN_IMPORT VARCHAR2(1 CHAR)
UNKNOWN_TRUSTED_FD VARCHAR2(1 CHAR)
COMPILE_STATE VARCHAR2(19 CHAR)
USE_NO_INDEX VARCHAR2(1 CHAR)
STALE_SINCE DATE
SELECT OWNER, MVIEW_NAME, CONTAINER_NAME, UPDATABLE, UPDATE_LOG, REFRESH_MODE, REFRESH_METHOD, LAST_REFRESH_TYPE, LAST_REFRESH_DATE AS LAST_DATE
FROM USER_MVIEWS
OWNER MVIEW_NAME CONTAINER_NAME U UPDATE_LOG REFRESH_MODE REFRESH_METHOD LAST_REFRESH_TYPE LAST_DATE
---------- ------------ -------------- - ---------- ------------ -------------- ----------------- ----------
ALEC_USER CONTACT_MV CONTACT_MV N DEMAND FORCE COMPLETE 22-SEP-14
ALEC_USER EMP_ADDR_MV EMP_ADDR_MV N DEMAND FORCE COMPLETE 17-SEP-14
ALEC_USER EMPLOYEE_MV EMPLOYEE_MV N DEMAND FORCE COMPLETE 23-SEP-14
ALEC_USER ADDRESS_MV ADDRESS_MV N DEMAND FORCE FAST 23-SEP-14
USER_MVIEW_REFRESH_TIMES
DESC USER_MVIEW_REFRESH_TIMES
SQL> DESC USER_MVIEW_REFRESH_TIMES
Name Type
------------ -------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
MASTER_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
LAST_REFRESH DATE
SELECT OWNER||'.'||NAME AS MVIEW_NAME, MASTER_OWNER||'.'||MASTER AS MASTER_TABLE, LAST_REFRESH
FROM USER_MVIEW_REFRESH_TIMES
ORDER BY LAST_REFRESH DESC;
MVIEW_NAME MASTER_TABLE LAST_REFRESH
--------------------- ------------------ ------------
ALEC_USER.EMPLOYEE_MV ALEC_USER.EMPLOYEE 23-SEP-14
ALEC_USER.ADDRESS_MV ALEC_USER.ADDRESS 23-SEP-14
ALEC_USER.CONTACT_MV ALEC_USER.CONTACT 22-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.ADDRESS 17-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.EMPLOYEE 17-SEP-14
USER_MVIEW_ANALYSIS
DESC USER_MVIEW_ANALYSIS
SQL> DESC USER_MVIEW_ANALYSIS
Name Type
----------------- -----------------
MVIEW_NAME VARCHAR2(30)
MVIEW_TABLE_OWNER VARCHAR2(30)
CONTAINER_NAME VARCHAR2(30)
LAST_REFRESH_SCN NUMBER
LAST_REFRESH_DATE DATE
REFRESH_METHOD VARCHAR2(8 CHAR)
SUMMARY VARCHAR2(1 CHAR)
FULLREFRESHTIM NUMBER
INCREFRESHTIM NUMBER
CONTAINS_VIEWS VARCHAR2(1 CHAR)
UNUSABLE VARCHAR2(1 CHAR)
RESTRICTED_SYNTAX VARCHAR2(1 CHAR)
INC_REFRESHABLE VARCHAR2(1 CHAR)
KNOWN_STALE VARCHAR2(1 CHAR)
INVALID VARCHAR2(1 CHAR)
REWRITE_ENABLED VARCHAR2(1 CHAR)
QUERY_LEN NUMBER
QUERY LONG
REVISION NUMBER
SELECT MVIEW_NAME AS MVIEW_NAME, MVIEW_TABLE_OWNER TABLE_OWNER, CONTAINER_NAME, LAST_REFRESH_DATE AS LAST_REFRESH, REFRESH_METHOD AS METHOD,
fullrefreshtim AS FULL_REFRESH_TIME,
increfreshtim AS INCRE_REFRESH_TIME
FROM USER_MVIEW_ANALYSIS;
MVIEW_NAME TABLE_OWNER CONTAINER_NAME LAST_REFRESH METHOD FULL_REFRESH_TIME INCRE_REFRESH_TIME
--------------------- ----------- -------------- ------------ -------- ----------------- -------------------
CONTACT_MV ALEC_USER CONTACT_MV 22-SEP-14 FORCE 0 0
EMP_ADDR_MV ALEC_USER EMP_ADDR_MV 17-SEP-14 FORCE 0 0
EMPLOYEE_MV ALEC_USER EMPLOYEE_MV 23-SEP-14 FORCE 0 0
ADDRESS_MV ALEC_USER ADDRESS_MV 23-SEP-14 FORCE 0 0
To display the start time and end time, use below calculation:
SELECT
MVIEW_NAME AS MVIEW_NAME,
TO_CHAR(last_refresh_date,'YYYY-MM-DD hh24:mm:ss') "START_TIME",
TO_CHAR(CASE
WHEN fullrefreshtim <> 0 THEN
LAST_REFRESH_DATE + fullrefreshtim/60/60/24
WHEN increfreshtim <> 0 THEN
LAST_REFRESH_DATE + increfreshtim/60/60/24
ELSE
LAST_REFRESH_DATE
END,'YYYY-MM-DD hh24:mm:ss') "END_TIME",
fullrefreshtim AS FULL_REFRESH_TIME,
increfreshtim AS INCREMENT_REFRESH_TIME
FROM USER_MVIEW_ANALYSIS;
MVIEW_NAME START_TIME END_TIME FULL_REFRESH_TIME INCREMENT_REFRESH_TIME
--------------------- -------------------- ------------------- ----------------- ----------------------
CONTACT_MV 2014-09-22 13:09:55 2014-09-22 13:09:55 0 0
EMP_ADDR_MV 2014-09-17 05:09:16 2014-09-17 05:09:16 0 0
EMPLOYEE_MV 2014-09-23 09:09:39 2014-09-23 09:09:39 0 0
ADDRESS_MV 2014-09-23 08:09:25 2014-09-23 08:09:25 0 0
Appendix
Useful Reference Sites
http://www.bluegecko.net/oracle/implementing-count-distinct-with-fast-refresh-materialized-views/
http://www.sqlsnippets.com/en/topic-12884.html
