Headache Problem In Material View ( MV) refresh

[ligang]

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 .

[Axr2]

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?

[ligang]

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

[reydp]

You might need to recreate ur MVs and perhaps MV logs again.

[ligang]

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")