Re: Materialized views not refreshing

Jared,

I joined this thread late; so I apologize if this suggestions has been tried already.

Sometimes the purge of teh MLOG$ tables leave the table in such fragmented state that the refresh takes a considerably long time and thus it appears that the MV are not refreshing. Could you try the following

Quiesce the master table(s)
Truncate the MLOG$ tables (not delete)
Unquiesce the master tables(s)
Refresh Full
Refresh Fast Manually once
then leave it to the jobns to do the refresh.

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, February 25, 2003 2:39 PM

>
> Yes, the jobs were firing, verified by timestamps in both
> the dba_jobs and dba_refresh views.
>
> On Tuesday 25 February 2003 05:33, Darrell Landrum wrote:
> > Is the job even firing at all? It is a common step in upgrading to set
> > job_queue_processes=0 and this will prevent materialized views from
> > refreshing automatically. Make sure this is greater than 0; I normally
set
> > it to 2.
> >
> > >>> sfaroult_at_oriolecorp.com 02/25/03 04:29AM >>>
> > >
> > >Dear list,
> > >
> > >Have any of you every experienced MV's not
> > >refreshing
> > >for no particular reason?
> > >
> > >We have been using some simple MV's for several
> > >months
> > >with no problem. Now after upgrading our app and
> > >database,
> > >there seem to be problems.
> > >
> > >Servers:
> > >
> > >Master: Win2k SP2 Oracle 8.1.7.4.1
> > >
> > >Slave: NT 4 SP6 Oracle 8.1.7.4.1
> > >
> > >These databases were previously 8.1.6. The master
> > >database
> > >now has a UTF8 character set, and all tables have
> > >NVARCHAR2
> > >columns.
> > >
> > >These are being converted in the MV using:
> > > translate("COLUMN_NAME" using char_cs)
> > >COLUMN_NAME
> > >when creating the MV.
> > >
> > >There are no errors, no trace files. Data is
> > >updated on the master
> > >node and never appears in the slave.
> > >
> > >The data continues to persist in the MLOG$ tables.
> > >I've verified there
> > >is only a single MV against each MV LOG, so the
> > >data in MLOG$ should
> > >be truncated after a refresh, but the fact that it
> > >isn't makes it fairly
> > >obvious that the refresh is not working properly.
> > >
> > >I have a serverity 1 TAR open now with Oracle, but
> > >so far all that's
> > >been accomplished with the TAR is me repeating
> > >everything I
> > >included initially.
> > >
> > >Any advice appreciated, as it's rather important to
> > >get this working again.
> > >
> > >Jared
> >
> > Jared,
> >
> > What about the refresh jobs ? Does DBA_JOBS show failures ? If so, can
> > you wrap the dbms_refresh call into something to catch the error ? (if
you
> > feel lazy I think that there is code to this effect on the Oriole site
in
> > one of the 'Aunt Augusta' papers).
> >
> > Regards,
> >
> > Stephane Faroult
> > Oriole
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).