Re: Mviews Refresh Timings
Date: Mon, 9 Mar 2009 08:20:46 -0700 (PDT)
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. ?
I just tested this in 126.96.36.199 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
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;
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