--1、给本地库授权grantcreatematerializedviewtoscott;--2、源系统库目标表建日志creatematerializedviewlogondept_tmp_test tablespace movebi_data--日志空间withprimarykey;--指定为主键类型无主键可指定rowid,但注意要和后面创建物化视图时保持一致--查看日志se
也可以通过物化视图实现跨表约束。 Drop Materialized View v_stope_All; CREATE MATERIALIZED VIEW v_stope_All(stope_id) REFRESH WITH ROWIDasselectstope_no From TB_STOPE Union All Select block_id From tb_block; create UNIQUE Index idx_v_stope_All on v_stope_All(stope_id);...
create index IDX_MMT_IU_TEST on mv_materialized_test(ID,UNAME) tablespace test_space; 1. 2. 3. 删除物化视图及日志 drop materialized view log on test_table; --删除物化视图日志: 1. drop materialized view mv_materialized_test; --删除物化视图 1. 物化视图刷新 使用dbms_mview.refresh 手工刷新...
在源数据库中对 6 张活跃表开启 Materialized View Log CREATE TABLESPACE MVLOG DATAFILE SIZE 1G AUTOEXTEND ON; CREATE MATERIALIZED VIEW LOG ON USR2.USPARAMETER TABLESPACE MVLOG WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON USR2.UPI_RESULT TABLESPACE MVLOG WITH ...
CREATEMATERIALIZEDVIEW mv_emp_pk REFRESHFASTSTARTWITHSYSDATE NEXT SYSDATE + 1/48 WITHPRIMARYKEY ASSELECT * FROM emp@remote_db --当用FAST选项创建物化视图,必须创建基于主表的视图日志,如下: CREATEMATERIALIZEDVIEWLOGON emp; --Rowid物化视图示例: ...
CREATE MATERIALIZED VIEW view_name [BUILD IMMEDIATE(默认值) | BUILD DEFERRED ] //物化数据什么时候生成 REFRESH [FAST|COMPLETE|FORCE[默认值]] [ ON [COMMIT |DEMAND] ] AS subquery 1. 2. 3. 4. 5. 物化数据的产生: [BUILD IMMEDIATE(默认值) | BUILD DEFERRED ] //物化数据什么时候生成 ...
ORA-12045: ALTER MATERIALIZED VIEW LOG 选项无效 ORA-12046: 无法将信任的约束条件用于刷新远程 MV ORA-12047: PCT FAST REFRESH 不能用于实体化视图 “”.”” ORA-12048: 刷新实体化视图 “”.”” 时出错 ORA-12051: ON COMMIT 属性与其它选项不兼容 ...
CREATEANYTRIGGERCREATEANYINDEXGRANTEXECUTE_CATALOG_ROLETOTEST;GRANTCREATETABLE,CREATEMATERIALIZEDVIEWTOTEST;GRANTCREATEANYTABLE,ALTERANYTABLE,DROPANYTABLE,LOCKANYTABLE,SELECTANYTABLETOTEST;GRANTCREATEYTRIGGER,CREATEANYINDEXTOTEST; 代码语言:javascript 代码运行次数:0 ...
-- 创建视图create materialized view [view_name]refresh [fast|complete|force][on [commit|demand] |start with (start_time) next (next_time)]as{创建物化视图时用的查询语句}-- 创建索引create unique index index_name on view_name (列名1,列名2...); ...
CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE AS SELECT * FROM sh.customers@remote cu WHERE EXISTS (SELECT * FROM sh.countries@remote co WHERE co.country_id = cu.country_id); The following example creates a materialized view on two source tables: times and product...