Headache Problem In Material View ( MV) refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Headache Problem In Material View ( MV) refresh

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Headache Problem In Material View ( MV) refresh

    Very Headache Problem .
    We got 2 database server . Live server and report server .
    the report server is using MV to get the data from Life server .
    the refresh mode is 'F' .

    There is some housekeeping batch running in life server .
    BUt actually , no data is purged because there is no 'old enough
    data' in life server . So when the housekeeping procedure running ,
    actually the delete statement is not purging anything .
    the funny things is although there is nothing deleted in life server , the MV refresh report server is hanging ! it simply executing for ever !

    Is there any thoughts to figure out what is the problem ?

    Oracle Database is 9.2.0.1.0 in unix .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    So is the refresh failing Or just taking very long? Any messages in the alertlogs? How big are the snapshot logs on the live server? Is there a lot of DML going on? Is it a humongous table? Any custom indexes on reporting table?

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    The Refresh is hanging and executing for ever .
    we have to kill the session and restart the database .
    the data volume is not so big . around 300 records in the remote table and the MLOG$_Table only records 800 records ( 800 changes I think ) .

    we have not yet check the alert log .
    the index is the Primy key only
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    You might need to recreate ur MVs and perhaps MV logs again.
    ---------------

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Fast Refresh , we got below error :
    ORA-12008: error in materialized view refresh path
    ORA-04031: unable to allocate 2048 bytes of shared memory ("large pool","unknown object","session heap","oacdef info")
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

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