oracle远程查询视图,oracle远程物化视图

一、创建远程物化视图日志

源端：

CREATE MATERIALIZED VIEW LOG ON tozwdb.test tablespace tozwdb_data WITH ROWID;

二、付权限

目标库

Create user tozwdb identified by tozwdb_dev;

Grant create session to tozwdb;

Grant resource to tozwdb;

Grant connect to tozwdb;

Grant unlimited tablespace to tozwdb;

Grant create database link to tozwdb;

Grant create public database link to tozwdb;

Grant create materialized view to tozwdb;

Grant select any table to tozwdb;

Grant create table to tozwdb;

查询权限：

select * from user_sys_privs t where t.privilege like upper(‘%link%‘);

三、创建DB_LINK

Create public database link db_pay connect to pay identified by pay

using ‘(DESCRIPTION= (ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST =172.16.13.25)(port=1521)))

(CONNECT_DATA = (SERVICE_NAME= ACCOUNT )))‘;

四、创建远程物化视图

Create materialized view tozwdb.T_IF_tozwdb TABLESPACE to pay_data

refresh fast ON DEMAND with rowid START WITH TO_DATE(‘08-02-2017 15:50:00‘, ‘DD-MM-YYYY HH24:MI:SS‘)

NEXT SYSDATE + 1/1440 as select * from [email protected]_pay;