-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|