Urgent!!! Materialize view issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Urgent!!! Materialize view issue

  1. #1
    Join Date
    Sep 2002
    Posts
    21

    Lightbulb Urgent!!! Materialize view issue

    Hi frens,
    Complete Refresh of a Materialize view takes lots of space in temporary tablespace. Need to avoid this circumstances ,Any suggestions???

    Thanks in advance.

    Sarav.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What kind of query do you have defining the view? I would expect that it's an aggregation, in which case you might set a large sort_area_size for the session before refreshing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2000
    Posts
    467
    Create a snapshot log, do a fast refresh !
    Vinit

  4. #4
    Join Date
    Sep 2002
    Posts
    21

    Lightbulb Materialized View

    Actually data consists in the base table is 100 millions and its single table aggregate MView.

    Any suggestions, how to reduce the MView eats up lots of space in temporary tablespace.

    Thanks in advance.
    Sarav.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How much space is it using up? To do a complete refresh is going to demand a certain amount of temp space, and there's not a lot you can do about it. You might consider not doing complete refresh, as vinit suggests. Perhaps you could describe how data is loaded to the master table and what the exact MV query is, and we could think about whether partitioning, and implementing partition change tracking would be beneficial.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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