Hi All,
Good Morning !
Below is materialised view code, refresh happens on daily basis. For past one year it was working without any issues and suddenly getting an error like "ORA-12008: error in materialized view refresh path ORA-01706: user function result value was too large"
Note : If I run the select statement then able to see the result only when its going for insert(MV Refresh) then its giving error.
Time taking to refresh : 4 to 5 hours
Total number Of Records : 1 million
DB version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
OS : Linux 2.4.xx x86_64
Kindly help me to resolve this issue. Thank you!
MView
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "MV_FORMS_MGMT_MONTHLY_DATA" ("FM_FORM_ID", "FM_FORMTYPE_ID", "FM_DATE", "MONTH_NUM", "VESSEL_CODE", "DATA_NODE", "DATA_POINT", "DATA_VALUE", "LAST_REFRESH_DATE", "DESCRIPTION")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_EBI_UAT_DATA1"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS with
data as
( select fm.fm_form_id,fm.fm_formtype_id,fm.fm_date,fm.vessel_code,ft.fm_periodicity_id,replace(replace(fm.fm_Data,'xmlns=''generic''',''),'<?xml version=''1.0'' encoding=''utf-8''?>','') XmlString from
forms_master fm ,
form_types ft
WHERE fm.fm_formtype_id = ft.fm_formtype_id
AND ft.fm_periodicity_id in (4)
)
select t.fm_form_id,t.fm_formtype_id,t.fm_date,to_number(to_char(t.fm_date,'YYYYMM')) month_num,t.vessel_code,replace(x.nams,'_',' ') data_Node,extractvalue(xmltype(t.XmlString),'//'||x.nams||'/@attr') Data_point, nvl(x.vals,0) data_value,sysdate last_refresh_date,'This MV stores MONTHLY forms Data' DESCRIPTION
from data t,
xmltable('for $n in //*[count(./*) = 0]
return
<paths>
<nams>{name($n)}</nams>
<vals>{$n}</vals>
</paths>
'
passing xmltype(t.XmlString)
columns nams varchar2(4000) path '/paths/nams',
vals varchar2(4000) path '/paths/vals'
) X
where is_number(x.vals)=1 and x.vals is not null and x.vals <> '0';
Kind Regards,
Mohan