ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重建物化视图及表的一些问题记录

重建物化视图及表的一些问题记录

原创 Linux操作系统 作者:47328983 时间:2011-04-16 20:59:30 0 删除 编辑

重建物化视图并进行刷新时遇到的一些问题及处理:

Q1:
Elapsed: 00:00:00.30
BEGIN dbms_mview.refresh('NSR_YHXX','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02291: integrity constraint (SYGL.FK_YHXX_REF_YH) violated - parent key not
found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

1.find the rows:
select * from NSR_YHXX_t t where not exists (select * from DM_YH o where o.YH_DM=t.YH_DM and  o.YHZL_DM=t.YHZL_DM);

2.refresh the father table
 p_mview_refreshs('DM_YH','C','Y');
 
3.retry to check the integrity constraint
select * from NSR_YHXX_t t where not exists (select * from DM_YH o where o.YH_DM=t.YH_DM and  o.YHZL_DM=t.YHZL_DM);
--have no rows
 
4.redo the dbms_mview.refresh('NSR_YHXX','C');

Q2:
Elapsed: 00:00:00.01
          from sb_zzs_xgmnsr_2005 t, pt_dim_nsr n
               *
ERROR at line 27:
ORA-00980: synonym translation is no longer valid


相互关联的表重建顺序:
SB_ZZS_2003_YBNSR->SB_YBNSR_ZZSXX->SB_ZZS_XSE

Q3:
Elapsed: 00:00:00.02
  add constraint FK_WSJMT_REF_JMTFSDM foreign key (JMTFS_DM)
                                                   *
ERROR at line 2:
ORA-02267: column type incompatible with referenced column type


Elapsed: 00:00:00.02
  add constraint FK_WSJMT_REF_JMYYDM foreign key (JMYY_DM)
                                                  *
ERROR at line 2:
ORA-02267: column type incompatible with referenced column type


Elapsed: 00:00:00.01
  add constraint FK_WSJMT_REF_SWJG foreign key (SWJG_DM)
                                                *
ERROR at line 2:
ORA-02267: column type incompatible with referenced column type


Elapsed: 00:00:00.01
  add constraint FK_WSJMT_REF_ZSXM foreign key (ZSXM_DM)
                                                *
ERROR at line 2:
ORA-02267: column type incompatible with referenced column type

字段类型不匹配,查看表与父表字段类型。

 

Q4:
Elapsed: 00:00:00.00
       MT.NSRSBH,
       *
ERROR at line 8:
ORA-12060: shape of prebuilt table does not match definition query

建立物化视图时,字段名称在子查询中未找到,需修改字段名与子查询相匹配。

Q5:
Elapsed: 00:00:00.04
       trunc(cjqx),
       *
ERROR at line 14:
ORA-12060: shape of prebuilt table does not match definition query

创建物化视图时字段的类型与基表字段类型不匹配。

 

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

上一篇: nologging建表
请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    333633