ORA-01555: snapshot too old on MView refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ORA-01555: snapshot too old on MView refresh

  1. #1
    Join Date
    Oct 2016
    Posts
    3

    ORA-01555: snapshot too old on MView refresh

    Dear all,

    I have couple jobs that refresh materialized views nightly. However, the MVs pull many data (80%) from the database and our space is very insufficient to support a large undo tablespace. I have been tuning UNDO_RETENTION to large numbers and enlarge the undo, but still not big enough.

    Is there a way we can break down the refresh on MVs to allow partial commits? Any help is appreciated.


    Thanks,
    Unna.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,157
    Ideally you have log files on every table in the materialized views and the rowid for each table in the query.
    Depending on the version of Oracle that you are using, you can then use the fast refresh feature. This will
    cause Oracle to change less data, and thus generate lass redo. I don't think that you can do partial commits,
    you can change less data though.

    https://docs.oracle.com/database/121....htm#DWHSG8382

    Also the more efficient the query for the materialized view, the better. You should avoid inline queries, and or's in the where clause.

    https://asktom.oracle.com/pls/asktom...85400346999596
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width