单纯的select语句可以查询出来,就是物化的时候报错create materialized view MV_ODS_TESTOLD refresh force on demand next sysdate+1/4 as
select a.serial_number,
o.order_item,
wmsys.wm_concat(a.ODS_DOUBLET) as ODS_DOUBLE,
wmsys.wm_concat(a.ODS_HORNT) as ODS_HORN,
wmsys.wm_concat(a.ODS_FROCET) as ODS_FROCE,
wmsys.wm_concat(a.ODS_WEIGHTT) as ODS_WEIGHT,
wmsys.wm_concat(a.ODS_DETECTIONT) as ODS_DETECTION,
wmsys.wm_concat(a.ODS_RESTIANCET) as ODS_RESTIANCE,
wmsys.wm_concat(a.ODS_SB_RESTIANCET) as ODS_SB_RESTIANCE,
wmsys.wm_concat(a.ODS_SINGLET) as ODS_SINGLE,
wmsys.wm_concat(a.ODS_SW_TESTT) as ODS_SW_TEST,
o.planned_line,
o.part_number,
a.unit_key
from (
select u.unit_key,u.serial_number,u.order_item_key,
d.op_name||'$'||d.dscomment||'$'||to_char(d.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||d.angle||'+'||d.torque||'+' as ODS_DOUBLET,
h.op_name||'$'||h.dscomment||'$'||to_char(h.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||h.anxiangli||'+' as ODS_HORNT,
f.op_name||'$'||f.dscomment||'$'||to_char(f.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||f.froce_test||'+' as ODS_FROCET,
t.op_name||'$'||t.dscomment||'$'||to_char(t.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||t.inflatorweight||'+' as ODS_WEIGHTT,
e.op_name||'$'||e.dscomment||'$'||to_char(e.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||e.influential||'+' as ODS_DETECTIONT,
r.op_name||'$'||r.dscomment||'$'||to_char(r.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||r.jueyuan||'+'||r.neizu||'+' as ODS_RESTIANCET,
s.op_name||'$'||s.dscomment||'$'||to_char(s.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||s.sb_resistance||'+' as ODS_SB_RESTIANCET,
i.op_name||'$'||i.dscomment||'$'||to_char(i.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||i.angle||'+'||i.torque||'+' as ODS_SINGLET,
w.op_name||'$'||w.dscomment||'$'||to_char(w.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||w.swtest||'+' as ODS_SW_TESTT
from unit u
left join dc_double_torque d on u.unit_key=d.object_key
left join dc_horn h on u.unit_key=h.object_key
left join dc_froce_test f on u.unit_key=h.object_key
left join dc_inflatorweight t on u.unit_key=t.object_key
left join dc_influentialdetection e on u.unit_key=e.object_key
left join dc_restiance r on u.unit_key=r.object_key
left join dc_sb_resistance s on u.unit_key=s.object_key
left join dc_single_torque i on u.unit_key=i.object_key
left join dc_swtest w on u.unit_key=w.object_key
order by u.serial_number
)a,work_order_items o
where o.order_item_key= a.order_item_key
group by a.serial_number,o.order_item,o.planned_line,o.part_number,a.unit_key度娘后 有人说是修改oracle的一个隐含参数“_system_trig_enabled”用来控制系统触发器的。
alter system set "_system_trig_enabled"=false
修改之后依旧无效。
现在how do?
修改物化视图的属性?
select a.serial_number,
o.order_item,
wmsys.wm_concat(a.ODS_DOUBLET) as ODS_DOUBLE,
wmsys.wm_concat(a.ODS_HORNT) as ODS_HORN,
wmsys.wm_concat(a.ODS_FROCET) as ODS_FROCE,
wmsys.wm_concat(a.ODS_WEIGHTT) as ODS_WEIGHT,
wmsys.wm_concat(a.ODS_DETECTIONT) as ODS_DETECTION,
wmsys.wm_concat(a.ODS_RESTIANCET) as ODS_RESTIANCE,
wmsys.wm_concat(a.ODS_SB_RESTIANCET) as ODS_SB_RESTIANCE,
wmsys.wm_concat(a.ODS_SINGLET) as ODS_SINGLE,
wmsys.wm_concat(a.ODS_SW_TESTT) as ODS_SW_TEST,
o.planned_line,
o.part_number,
a.unit_key
from (
select u.unit_key,u.serial_number,u.order_item_key,
d.op_name||'$'||d.dscomment||'$'||to_char(d.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||d.angle||'+'||d.torque||'+' as ODS_DOUBLET,
h.op_name||'$'||h.dscomment||'$'||to_char(h.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||h.anxiangli||'+' as ODS_HORNT,
f.op_name||'$'||f.dscomment||'$'||to_char(f.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||f.froce_test||'+' as ODS_FROCET,
t.op_name||'$'||t.dscomment||'$'||to_char(t.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||t.inflatorweight||'+' as ODS_WEIGHTT,
e.op_name||'$'||e.dscomment||'$'||to_char(e.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||e.influential||'+' as ODS_DETECTIONT,
r.op_name||'$'||r.dscomment||'$'||to_char(r.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||r.jueyuan||'+'||r.neizu||'+' as ODS_RESTIANCET,
s.op_name||'$'||s.dscomment||'$'||to_char(s.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||s.sb_resistance||'+' as ODS_SB_RESTIANCET,
i.op_name||'$'||i.dscomment||'$'||to_char(i.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||i.angle||'+'||i.torque||'+' as ODS_SINGLET,
w.op_name||'$'||w.dscomment||'$'||to_char(w.creation_time,'yyyy-mm-dd hh24:mi:ss')||'+'||w.swtest||'+' as ODS_SW_TESTT
from unit u
left join dc_double_torque d on u.unit_key=d.object_key
left join dc_horn h on u.unit_key=h.object_key
left join dc_froce_test f on u.unit_key=h.object_key
left join dc_inflatorweight t on u.unit_key=t.object_key
left join dc_influentialdetection e on u.unit_key=e.object_key
left join dc_restiance r on u.unit_key=r.object_key
left join dc_sb_resistance s on u.unit_key=s.object_key
left join dc_single_torque i on u.unit_key=i.object_key
left join dc_swtest w on u.unit_key=w.object_key
order by u.serial_number
)a,work_order_items o
where o.order_item_key= a.order_item_key
group by a.serial_number,o.order_item,o.planned_line,o.part_number,a.unit_key度娘后 有人说是修改oracle的一个隐含参数“_system_trig_enabled”用来控制系统触发器的。
alter system set "_system_trig_enabled"=false
修改之后依旧无效。
现在how do?
修改物化视图的属性?
解决方案 »
- oracle 分割字符串 regexp_substr
- [经验分享] 如何在JAVA程序中使用Struct一次传入多条数据给Oracle的存储过程。
- 小弟有个项目,大数据,现在风雨飘摇地坚持着
- 请高手看看触发器问题!!!!!!!!!
- 求教!:备份整个数据库后数据、日志、控制文件全部删除后怎么恢复数据库啊?
- 如何用java程序来调用PL/SQL????
- 请问各位大侠!!我怎么在过程中无法访问sys.dba_data_files这个表呢.用单独的SQL语句都可以查看!(都在同一用户下)
- 关于更新 如何对 两个表关联后查出的记录的某个表栏位进行更新???
- 再问初级问题:)
- 用一条语句把一个表的数据全部导入另一个表(两个表结构一样),怎么写?
- 创建触发器出现PLS-00103: 错误
- SQL优化以及算法难题求解...
原因之二:声明的变量类型在赋值的时候与数据库中字段的类型不匹配。大家可以使用 variable_name table_name.col_name%TYPE可以避免此类问题。http://blog.csdn.net/xiyuting/article/details/7636492
1:wmsys.wm_concat不是无限制长度,当太长之后,就会报告你看到的异常.
2: 你执行单独sql的时候,可能恰巧只是返回了一些,真正执行的时候依然会报告wmsys.wm_concat的问题。也就是说根本上你的连接的内容超越的wm_concat的限制了。