物化视图创建过程

1.建立测试用户
主：create user bs identified by oracle;
    create user bs_query identified by oracle;
    grant dba to bs;
    grant dba to bs_query;
    
备：create user rep identified by oracle;
    grant dba to rep;




2. 创建测试表
备： create table rep.test（id number,name varchar(10));
insert into rep.test values (1,'a');
insert into rep.test values (2,'b');
commit;




3.BS 建立dblink
create public database link rep_test connect to rep identified by oracle
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =oggtest)
)
)';






4，另加的权限
GRANT CREATE MATERIALIZED VIEW TO bs;
GRANT CREATE ANY TABLE TO BS;
GRANT SELECT ANY TABLE TO BS;
GRANT COMMENT ANY TABLE TO BS;
GRANT SELECT ANY DICTIONARY TO BS;


5.创建物化视图
在源表创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON test
TABLESPACE users           
WITH rowid;  


在目标创建物化视图
CREATE MATERIALIZED VIEW mv_reptest
REFRESH FAST
WITH ROWID
ON DEMAND 
START WITH SYSDATE
ENABLE QUERY REWRITE AS
select * from rep.test@rep_test; 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29656486/viewspace-1161585/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29656486/viewspace-1161585/