本文共 1952 字，大约阅读时间需要 6 分钟。

   
	
    
Oracle????Materialized View?MV??Stream?GoldenGate??
	
    
Materialized View?MV???????
	
    
1. ????
	
    
???`test`???
	```sql	cREATE TABLE test (		id VARCHAR2(10) NOT NULL PRIMARY KEY,		name VARCHAR2(20),		status VARCHAR2(1),		updatedate DATE	);	```	
    
2. ????????DBLink?
	
    
??DBLink?B????
	```sql	CREATE DATABASE LINK dblink_to_B connect to "userid" identified by "password" using ('(DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = "ipaddress")(PORT = 1521)) ) (CONNECT_DATA =  (SERVICE_NAME = "SID") ) );	```	
    
??DBLink?A????
	```sql	CREATE DATABASE LINK dblink_to_A connect to "userid" identified by "password" using ('(DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = "ipaddress")(PORT = 1521)) ) (CONNECT_DATA =  (SERVICE_NAME = "SID") ) );	```	
    
3. ??Materialized View?MV?????
	
    
??MV???
	```sql	CREATE MATERIALIZED VIEW LOG ON test WITH ROWID;	```	
    
??MV????????
	```sql	CREATE MATERIALIZED VIEW mv_test REFRESH FAST ON DEMAND AS 	SELECT * FROM test@dblink_to_B;	```	
    
4. ?????Materialized View?MV?????
	
    
??MV???
	```sql	CREATE MATERIALIZED VIEW LOG ON test WITH ROWID;	```	
    
??MV????????
	```sql	CREATE MATERIALIZED VIEW mv_test REFRESH FAST ON DEMAND AS 	SELECT * FROM test@dblink_to_A;	```	
    
5. ?????????
	
    
??PLSQL?????????
	```sql	DBMS_MVIEW.REFRESH('mv_test', 'F');	```	
    
??????
	```sql	SELECT COUNT(*) INTO v_count FROM mv_test WHERE ROWNUM < 2;	```	
    
?????????
	```sql	IF v_count > 0 THEN		MERGE INTO test a USING (SELECT id, name, status, updatedate FROM mv_test) b		ON (a.id = b.id)		WHEN MATCHED THEN		 UPDATE SET a.name = b.name, a.status = b.status, a.updatedate = b.updatedate		WHERE a.updatedate < b.updatedate		WHEN NOT MATCHED THEN		 INSERT (id, name, status, updatedate) VALUES (b.id, b.name, b.status, b.updatedate);		COMMIT;		END IF;		EXCEPTION		WHEN OTHERS THEN			ROLLBACK;	```	
    
6. ????????????
	
    
?????????
	
    
????????MV????????????????
   

转载地址：http://kxpfk.baihongyu.com/