1.ROWIDWRITE表結構如下:---位於MYTEST01上
CREATE TABLE WISON.ROWIDWRITE
(
ID INTEGER NOT NULL,
NAME VARCHAR2(20 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
2. 先建立物化視圖日誌.---如果存在多個需要同步,則建立多個. ---在MYTEST01上建立
CREATE MATERIALIZED VIEW LOG ON WISON.ROWIDWRITE
TABLESPACE USERS
PCTUSED 0
PCTFREE 60
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;
2.建立物化視圖---在目的數據庫上創建 在目的數據庫上創建DBLINK MYTEST 連接到MYTEST01
CREATE MATERIALIZED VIEW WISON.ROWIDWRITE
TABLESPACE USERS
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
FOR UPDATE
AS
/* FORMATTED ON 2010/10/14 下午 03:30:34 (QP5 V5.115.810.9015) */
SELECT ROWIDWRITE. NAME, ROWIDWRITE.ID
FROM WISON.ROWIDWRITE@MYTEST ROWIDWRITE;
3. 創建刷新組---在目的數據庫上創建
DECLARE
SNAPARRAY SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
SNAPARRAY(1) := 'WISON.ROWIDWRITE';
SNAPARRAY(2) := NULL;
SYS.DBMS_REFRESH.MAKE (
NAME => 'WISON.ROWIDWRITE'
,TAB => SNAPARRAY
,NEXT_DATE => TO_DATE('10/19/2010 20:00:00', 'MM/DD/YYYY HH24:MI:SS')
,INTERVAL => 'SYSDATE + 6/1440 '
,IMPLICIT_DESTROY => TRUE
,LAX => TRUE
,JOB => 0
,ROLLBACK_SEG => NULL
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,HEAP_SIZE => 0
);
COMMIT;
END;
/
4. 之後每6分鐘就會開始進行兩邊數據同步(會自動創建同步的JOB)
CREATE TABLE WISON.ROWIDWRITE
(
ID INTEGER NOT NULL,
NAME VARCHAR2(20 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
2. 先建立物化視圖日誌.---如果存在多個需要同步,則建立多個. ---在MYTEST01上建立
CREATE MATERIALIZED VIEW LOG ON WISON.ROWIDWRITE
TABLESPACE USERS
PCTUSED 0
PCTFREE 60
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;
2.建立物化視圖---在目的數據庫上創建 在目的數據庫上創建DBLINK MYTEST 連接到MYTEST01
CREATE MATERIALIZED VIEW WISON.ROWIDWRITE
TABLESPACE USERS
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
FOR UPDATE
AS
/* FORMATTED ON 2010/10/14 下午 03:30:34 (QP5 V5.115.810.9015) */
SELECT ROWIDWRITE. NAME, ROWIDWRITE.ID
FROM WISON.ROWIDWRITE@MYTEST ROWIDWRITE;
3. 創建刷新組---在目的數據庫上創建
DECLARE
SNAPARRAY SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
SNAPARRAY(1) := 'WISON.ROWIDWRITE';
SNAPARRAY(2) := NULL;
SYS.DBMS_REFRESH.MAKE (
NAME => 'WISON.ROWIDWRITE'
,TAB => SNAPARRAY
,NEXT_DATE => TO_DATE('10/19/2010 20:00:00', 'MM/DD/YYYY HH24:MI:SS')
,INTERVAL => 'SYSDATE + 6/1440 '
,IMPLICIT_DESTROY => TRUE
,LAX => TRUE
,JOB => 0
,ROLLBACK_SEG => NULL
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,HEAP_SIZE => 0
);
COMMIT;
END;
/
4. 之後每6分鐘就會開始進行兩邊數據同步(會自動創建同步的JOB)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-676297/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16754036/viewspace-676297/