Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Commit snapshot rows after set amount processed

    Hey,

    I am creating several snapshots with around 10 millions rows in each and I was wondering is there a way to have a commit executed when a set amount of rows are processed during a complete refresh for each snapshot so that the undo space is kept to a minimum. For example, how would I execute a commit when the snapshot is refreshing at around 100000 rows?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Have you tried creating the materialized view with the NOLOGGING option?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is there a way to have a commit executed when a set amount of rows are processed
    This approach is a REAL GOOD way to generate ORA-01555 errors.
    visit http://asktom/oracle.com & do keyword search on "ORA-01555"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2004
    Posts
    74
    Thanks for the posts.

    I think I now know a little more about what's happening when the materialized views are refreshing. A huge undo file is being generated and it is using up all of the available disk space, is there any way to turn this functionality off?

    Thanks

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Have you tried lowering your undo retention period ? Also, just set a size for your undo tablespace (don't set it to auto-extend).

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Check out the following link for help

    http://asktom.oracle.com/pls/ask/f?p...15695764787749
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2004
    Posts
    74
    We did set a size for the undo tablespace, but then the snapshots would just hang when trying to refresh and the tablespace was full. And it appears that each time a snapshot refreshes more and more undo tablespace is used, and it never seems to release any of it when the snapshot is refreshed. Any more ideas on what's happening here?

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The space is released when then retention time expires. To release it earlier reduce your retention period (though this will reduce flashback), however it wont release the actual space allocated in the file unless you recreate the undo tablespace.

    To reduce undo space used you can try to reduce the amount of data held in the MV (remove redundent columns) or dont do a full refresh.

    Alan

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Did you read the information at asktom that was contained in the link above? It describes a number of ways to greatly reduce the about of redo on a complete refresh.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Dec 2004
    Posts
    74
    I did have a look through the asktom page, but he is talking more about redo then undo, does each follow the same specifics?

    Also, is a undo tablespace necessary, can it be dropped altogether and the snapshots still refresh normally?

Posting Permissions

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