第一步create materialized view log on cux_ecaigou_pipei_test with PRIMARY KEY;
第二步create materialized view CUX_DAORUBIANHAO_TEST
refresh fast on demand
start with sysdate next SYSDATE + 1/6 WITH PRIMARY KEY
as
select
t.id,
t.ITEMCODE,
t. DAORU_BIAOHAO ,
t. HANSHUI_DANJIA ,
t. FENPEISHULIANG ,
t. KUCUN_BIANMA ,
t. KUCUN_NAME ,
t. BAOJIA_DANHAO ,
t. XUYAORIQI ,
t. CHENGNUORIQI ,
t. ZIKUCUN_NAME ,
t. ZIKUCUN_BIANMA ,
t. CREATE_BY ,
t. CREATE_DATE ,
t. LASTUPDATE_BY ,
t. LASTUPDATE_DATE ,
t. STATUS ,
t. ATTRIBUTE1 ,
t. ATTRIBUTE2 ,
t. ATTRIBUTE3 ,
t. ATTRIBUTE4 ,
t. ATTRIBUTE5 ,
t. YEWUBU
from cux_ecaigou_pipei_test t;
第三步create MATERIALIZED VIEW LOG ON apps1.cux_daorubianhao_test
WITH primary key INCLUDING NEW VALUES;
第四步CREATE MATERIALIZED VIEW mv_emp_pkbuild immediate
refresh fast on demand
start with sysdate next SYSDATE + 1/6
WITH PRIMARY KEY as select
t1.id id1,
t1.itemcode itemcode1,
t.id ,
t.rowid b,
t.ITEMCODE,
t. DAORU_BIAOHAO ,
t. HANSHUI_DANJIA ,
t. FENPEISHULIANG ,
t. KUCUN_BIANMA ,
t. KUCUN_NAME ,
t. BAOJIA_DANHAO ,
t. XUYAORIQI ,
t. CHENGNUORIQI ,
t. ZIKUCUN_NAME ,
t. ZIKUCUN_BIANMA ,
t. CREATE_BY ,
t. CREATE_DATE ,
t. LASTUPDATE_BY ,
t. LASTUPDATE_DATE ,
t. STATUS ,
t. ATTRIBUTE1 ,
t. ATTRIBUTE2 ,
t. ATTRIBUTE3 ,
t. ATTRIBUTE4 ,
t. ATTRIBUTE5 ,
t. YEWUBU
from apps1.cux_daorubianhao_test t,
apps1.cux_ecaigou_pipei_test t1
where t1.id = t.id
前面的三步都成功了。为啥第四步提示出错了cannot fast refresh MATERIALIZED VIEW mv_emp_pk请高手帮忙看看是什么问题!!
第二步create materialized view CUX_DAORUBIANHAO_TEST
refresh fast on demand
start with sysdate next SYSDATE + 1/6 WITH PRIMARY KEY
as
select
t.id,
t.ITEMCODE,
t. DAORU_BIAOHAO ,
t. HANSHUI_DANJIA ,
t. FENPEISHULIANG ,
t. KUCUN_BIANMA ,
t. KUCUN_NAME ,
t. BAOJIA_DANHAO ,
t. XUYAORIQI ,
t. CHENGNUORIQI ,
t. ZIKUCUN_NAME ,
t. ZIKUCUN_BIANMA ,
t. CREATE_BY ,
t. CREATE_DATE ,
t. LASTUPDATE_BY ,
t. LASTUPDATE_DATE ,
t. STATUS ,
t. ATTRIBUTE1 ,
t. ATTRIBUTE2 ,
t. ATTRIBUTE3 ,
t. ATTRIBUTE4 ,
t. ATTRIBUTE5 ,
t. YEWUBU
from cux_ecaigou_pipei_test t;
第三步create MATERIALIZED VIEW LOG ON apps1.cux_daorubianhao_test
WITH primary key INCLUDING NEW VALUES;
第四步CREATE MATERIALIZED VIEW mv_emp_pkbuild immediate
refresh fast on demand
start with sysdate next SYSDATE + 1/6
WITH PRIMARY KEY as select
t1.id id1,
t1.itemcode itemcode1,
t.id ,
t.rowid b,
t.ITEMCODE,
t. DAORU_BIAOHAO ,
t. HANSHUI_DANJIA ,
t. FENPEISHULIANG ,
t. KUCUN_BIANMA ,
t. KUCUN_NAME ,
t. BAOJIA_DANHAO ,
t. XUYAORIQI ,
t. CHENGNUORIQI ,
t. ZIKUCUN_NAME ,
t. ZIKUCUN_BIANMA ,
t. CREATE_BY ,
t. CREATE_DATE ,
t. LASTUPDATE_BY ,
t. LASTUPDATE_DATE ,
t. STATUS ,
t. ATTRIBUTE1 ,
t. ATTRIBUTE2 ,
t. ATTRIBUTE3 ,
t. ATTRIBUTE4 ,
t. ATTRIBUTE5 ,
t. YEWUBU
from apps1.cux_daorubianhao_test t,
apps1.cux_ecaigou_pipei_test t1
where t1.id = t.id
前面的三步都成功了。为啥第四步提示出错了cannot fast refresh MATERIALIZED VIEW mv_emp_pk请高手帮忙看看是什么问题!!
解决方案 »
- 怎么才知道DB是建在哪个表空间?
- ORA-00600: internal error code, arguments: [kkpoStateObject], [3], [], [], [], [
- oracle中update,insert的使用
- 请问该存储过程用oracle怎么写啊!50
- 如何检索存储过程或包中的匹配字段?
- 原来是SQL server数据库和对应的程序,现在要用oracle数据库,要做哪些工作?
- 用存储过程如何创建表temp_table???
- ibatis查询语句不执行
- 用户问题!
- oracle的查询问题!
- 当执行一条Query语句时,发生"Out of Memory"
- 如何创建与视图同名的表?
TABLESPACE FLASH_RECOVE_TEST
BUILD deferred
REFRESH force on demand
START WITH sysdate --to_date('2010-10-20 03:23:00','yyyy-mm-dd hh24:mi:ss')
NEXT SYSDATE + 1/48
WITH PRIMARY KEY -- 或者 with rowid:
AS
SELECT * FROM TTT2;
CREATE MATERIALIZED VIEW LOG ON TTT2 tablespace FLASH_RECOVE_TEST with primary key ;1.物化视图的查询速度是普通视图的80倍;2.为了提高刷新速度:必须在创建物化视图日志的时候指定rowid或primary key
CREATE MATERIALIZED VIEW LOG ON TEST_TABLE tablespace t_sp_2
WITH ROWID[PRIMARY KEY];3.如果多表关联,必须在 每个表上建有相应的视图日志;4.实体化视图中不能包含 RAW or LONG 数据类型.5.当为基表创建视图日志后,如何让对基表的操作不计入MLOG$_xxx 表中呢?
方法如下:在DBA用户权限下执行:
SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_REORG');