Re: Mviews Refresh Timings

From: ddf <oratune_at_msn.com>
Date: Mon, 9 Mar 2009 08:20:46 -0700 (PDT)
Message-ID: <7578ce74-3436-44fc-b52c-45efde09d589_at_e24g2000vbe.googlegroups.com>

On Mar 9, 8:47 am, "lyx..._at_gmail.com" <lyx..._at_gmail.com> wrote:
> Thanks David for suggestion,
>
> What I am thinking to create one trigger on system level on "Truncate"
> specifically if segment is one of these
> Mview. I can just create this trigger before starting this job.
>
> As per my knowledge whenever we do complete refresh, Oracle first
> truncate the base mview and then will insert
> data into this.
>
> So - trigger will help when truncate happened and later on I can track
> dba_mview,refresh_Time when it was completed.
>
> Do you see this approach feasible/possible. ?
>
> Regards,

I just tested this in 11.1.0.6 and the refresh job doesn't fire the trigger to populate the log table:

SQL> create or replace trigger mv_refresh_trg   2 after truncate on schema
  3 begin

  4     INSERT INTO bing.mv_refresh_log
  5     ( SELECT
  6             ORA_DICT_OBJ_NAME,
  7             sysdate
  8        FROM SYS.DUAL,
  9             SYS.V_$SESSION
 10       WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );

 11 END;
 12 /

Trigger created.

SQL> exec dbms_refresh.refresh('EMP_MV')

PL/SQL procedure successfully completed.

SQL> select * from mv_refresh_log;

no rows selected

SQL> You'll need to find another way, I think, to do this.

David Fitzjarrell Received on Mon Mar 09 2009 - 10:20:46 CDT