IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS (PART -02)
MATERIALIZED VIEW WITH REFRESH FAST :
Now that we know how materialized view logs track changes to base tables we can use them to perform fast materialized view refreshes, i.e. refreshes where only the individual materialized view rows affected by base table changes are updated. This is also called “incremental” refreshing.
DBA 11 : SERVER SIDE CONFIGURATION:
Connect to U1 server.
|
1 2 3 4 5 6 7 8 9 |
SYS>>conn u1/u1 U1>>select * from tab; TNAME TABTYPE CLUSTERID ---------- ------- ---------- SALGRADE TABLE EMP TABLE DUMMY TABLE DEPT TABLE BONUS TABLE |
Add Primary Key on DEPT table:
|
1 2 3 |
U1>>alter table dept add primary key(deptno); Table altered. |
Create Materialized view log on top of DEPT table.
|
1 2 3 |
U1>>create materialized view log on dept; Materialized view log created. |
Now check tables under U1 Schema.
|
1 2 3 4 5 6 7 8 9 10 11 |
U1>>select * from tab; TNAME TABTYPE CLUSTERID ---------- ------- ---------- BONUS TABLE DEPT TABLE DUMMY TABLE EMP TABLE MLOG$_DEPT TABLE RUPD$_DEPT TABLE SALGRADE TABLE |
Now on Client Side create materialized view with fast option.
DBA 12 :CLIENT SIDE CONFIGURATION:
Implementing stream replication using MATERIALIZED VIEW LOG:
|
1 2 3 |
DL>>create materialized view mvfast refresh fast with primary key start with sysdate next sysdate+2/(24*60*60) as select * from dept@db01; Materialized view created. |
Try to select materialized view (MV FAST)
|
1 2 3 4 5 6 7 8 |
DL>>select * from mvfast; DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
Data was replicated to MV.
Now we can check mv log table.
DBA:3: SERVER SIDE CONFIGURATION:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
U1>>desc mlog$_dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NUMBER(2) SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) XID$$ NUMBER U1>>select * from dept; DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
Try to Insert some Records on DEPT table:
|
1 2 |
U1>>insert into dept (deptno) values (50); 1 row created. |
Check records under mlog$_dept.
|
1 2 3 4 5 6 |
U1>>select deptno from mlog$_dept; DEPTNO ------- 50 U1>>commit; Commit complete. |
DBA 11 :CLIENT SIDE CONFIGURATION:
Try to Check Records updated or not:
|
1 2 3 4 5 6 7 8 |
DL>>select * from mvfast; DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 |
Loading...
