ORA-12008: error in materialized view refresh path
I have a materialized view:
-- Create Materialized View
CREATE MATERIALIZED VIEW "MVW_ASSET"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)
LOB ("TOTAL") STORE AS (
ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
CACHE
BUILD DEFERRED
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT OID, STAGE, REVISION_NUMBER, REVISION_ROOT_OID, OBJECT_REFERENCE, ORIGINAL_CREATION_TIMESTAMP, MDS_OID, ASSET_REMARK, DEACTIVATION_DATE, FILE_FORMAT, ZERO_FORMAT_CATEGORY, PRJ_OID, VISIBLE_IN_AOC, WHATSNEW_TIMESTAMP, ASSET_NUMBER, TOTAL, NUMTOTAL FROM VW_ASSET;
But when I want to refresh it, I get this error
Error starting at line 1 in command:
BEGIN
dbms_mview.refresh('MVW_ASSET');
ctx_ddl.sync_index('WILDCARD_IDX');
ctx_ddl.sync_index('MVW_AST_NUM_TTL_IDX');
end;
Error report:
ORA-12008: error in materialized view refresh path
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2
12008. 00000 - "error in materialized view refresh path"
*Cause: Table SNAP$_<mview_name> reads rows from the view
MVIEW$_<mview_name>, which is a view on the master table
(the master may be at a remote site). Any
error in this path will cause this error at refresh time.
For fast refreshes, the table <master_owner>.MLOG$_<master>
is also referenced.
*Action: Examine the other messages on the stack to find the problem.
See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
<mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
still exist.
I dont know how to catch the exception to find out why the buffer is too small. CLOB can have 4G!