CODEWITHSUNDEEP
oracle-databasematerialized-views
Avi
Avi

Reputation: 1145

Refreshing Materialzed View in Oracle

I have created materialized view (MV) on a table which is updated once in a month.. Do we have any automatic way to refresh my MV... I mean how refresh of MV is done is it manual or Automatic how frequent we can do???? like can i use Trigger for it..??

I am using Oracle9i on PL/SQL developer Thanks

Upvotes: 1

Views: 2553

Answers (1)

Jonathan
Jonathan

Reputation: 12015

You can refresh the view manually if you want

execute DBMS_SNAPSHOT.REFRESH( 'MAT_VIEW','OPTION');

Where the OPTION parameter could be

F, f Fast Refresh
C, c Complete Refresh
A Always perform complete refresh
? Use the default option

The automatic refresh rate are supplied when you create the materialized view

    CREATE MATERIALIZED VIEW MAT_VIEW
    REFRESH FAST START WITH SYSDATE 
    NEXT  SYSDATE + TIME_INTERVAL -- 
    WITH PRIMARY KEY 
    AS SELECT * FROM TABLE;

Remember to create a log for the table if FAST START are used.

I recomend you the following link about Materialized View. It has a lot of info about this topic: http://www.dba-oracle.com/art_9i_mv.htm

Upvotes: 2

Related Questions