I've a Materialized view set to refresh on demand:
CREATE MATERIALIZED VIEW XYZ
REFRESH COMPLETE ON DEMAND
AS
SELECT * FROM ABC WHERE LAST_UPD > SYSDATE-30;
When i run a procedure for refresh it fails every two days.
Refresh command:
dbms_mview.refresh(list => 'XYZ',
method => 'C',
parallelism => 0,
atomic_refresh => false);
Error:
1 - ERROR IN MERGE : ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback segment number 406 with name "_SYSSMU406_3487494604$" too small ORA-02063: preceding line from IJSFASIEBEL
I've read that using select * to create the Materialized view can cause this error, but i've dropped the view and recreated it many times, the refresh runs fine one day and gets erred out the next day. No changes were made to the base table.
Can anyone tell me what the error message means or what might be causing the issue?
The problem is that your rollback segments are not large enough for the query that is being run given the other updates happening on the database at the same time.
There is a full discussion of what this means here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923
Possible solutions:-
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments