0

I want to automatically refresh the materialized view after the data is insert to tables when creating a materialized view.

I tried the following code (refresh complete start with Creating materialized view that refreshes every 5 min )but not working. Also this code is solution to refresh to every 1 minute.

I want a solution when inserting data. Is it possible?

Create Materialized view temp_mv refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as select * from temp;

Gül ÖZCAN
  • 15
  • 6
  • did you try creating a trigger and explicitly refresh `view`? – pulse Jan 08 '19 at 11:36
  • Use a view instead of a materialized view. Then the data is always "fresh". – sticky bit Jan 08 '19 at 11:41
  • 1
    "not working" isn't very helpful. You seem to be looking for an [`on commit` refresh](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/refreshing-materialized-views.html#GUID-587D67C7-CD96-44CA-8B3B-597C39247F31); but look at the restrictions to see if your MV is suitable for that. [Read more](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/basic-materialized-views.html#GUID-11109A1B-1E8A-4F10-9BB3-DEB4D1AAEC36). – Alex Poole Jan 08 '19 at 11:52

1 Answers1

1

You can try this, but it will not work for more complicated views. 

  --create table
    create table temp (a int not null primary key);


    -- create table log; 
    create materialized view log on temp
    with primary key
    including new values;

    --create view 
    create materialized view temp_mv
    build immediate 
    refresh fast
    on commit
    as
    select * from temp;


    -- populate table 
    insert into  temp select level from dual connect by level <100;

    select * from temp_mv; -- no value 

    commit; -- view is refreshed


    select * from temp_mv; -- all values
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17