Oracle Materialized View Refresh fails with ORA-01555

2
votes

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?

2
materialized-viewsora-01555

2 Answers

0
votes

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:-

  • Create larger rollback segment to allow more changes to occur during the refresh without running out of rollback space
  • Creating an index on LAST_UPD to improve the speed of the query (if indeed it does)
  • Running the refresh at a quieter time of day
0
votes

Pratheek Ponnuru,

Please check if any LOB are there in the table , the check for lob corruption. If LOB are corrupted then this error used to come.... I had faced same issue recently, I check the corruption for all lobs in the table and post further investigation found some corrupted lob segments, which later I set to blob_null().

-- Milind Kale