Hi, I scripted this as an example -- this is the base table create table tb_source as select * from dba_objects where 1=0; -- pk on the table alter table tb_source add primary key (object_id); --mview log for fast refresh create materialized view log on tb_source; --mview CREATE MATERIALIZED VIEW mv_simple BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM tb_source; --this is my alert table for simplicity. this would be designed as AQ table with a call back on it create table tb_alert (name varchar2(255), event_date date); -- this is an trigger on mview. has been created as an statement level trigger -- could be created as a row level trigger if there is a need create or replace trigger my_alert_trigger after insert on mv_simple begin insert into tb_alert values (' Record Inserted',sysdate); end; -- sample insert insert into tb_source select * from dba_objects where object_id is not null and rownum<10000; commit; -- now on commit the mview would be refreshed and on the refresh of the mview, the tb_alert would have records. -- this trigger could do a AQ and there would be a pl/sql routine as a call back that would wake up on the message to do rest of processing hope this helps best regards sriram kumar On Tue, May 29, 2012 at 5:03 PM, rjamya <rjamya@xxxxxxxxx> wrote: > Thanks Sriram, well the idea is to _detect_ if there has been any change > (especially inserts), the process will figure out exactly which rows based > on status. sometimes we have tens of rows changed in a minute, sometimes it > has been a hundred k, so all we need to detect is " were there any inserts > done in the last mv refresh ", if so, we will trigger sleeping process, if > not we will let it sleep some more. > > Hopefully this week we will get to rest some ideas ... oh and you are not > late, we are still in the eval phase > > Thanks > Raj > > > On Sat, May 26, 2012 at 4:48 AM, Sriram Kumar <k.sriramkumar@xxxxxxxxx>wrote: > >> Hi, >> >> apologies for jumping in late. How about a row-level trigger on the mview >> that performs a enqueue of the PK on AQ ? and then the poller can do a look >> up based on the PK? >> >> > -- //www.freelists.org/webpage/oracle-l