Refresh a materialized view

July 25, 2017Posted inORACLE

To refresh a Materialized view we need DBMS_SNAPSHOT.REFRESH procedure.

Steps.

CREATE TABLE demo_m
(
   id     NUMBER (10) primary key,
   name   VARCHAR2 (400)
);
/
INSERT INTO DEMO_M (
   ID, NAME)
VALUES (1001, 'VIKAS' );
/
SELECT * FROM demo_m;

One record is available in the table.

Create Materialized view.

CREATE MATERIALIZED VIEW emp_m_view
--BUILD IMMEDIATE --
REFRESH
--Fast
--Complete
FORCE
ON --COMMIT
DEMAND
AS
SELECT * FROM demo_m;
/
Select * from  emp_m_view;

One record is also in view. Now insert into table.

INSERT INTO DEMO_M (
   ID, NAME)
VALUES (1002, 'PANDEY' );
SELECT * FROM demo_m;

One record added.

check view-

Select * from  emp_m_view;

Still one record which was inserted before creating materialized view. now to refresh this records in view we need to run the DBMS_SNAPSHOT.REFRESH procedure.

BEGIN
         DBMS_SNAPSHOT.REFRESH('emp_m_view'); -- View name as parameter
END;
/

Check view data.

Select * from  emp_m_view;

Now data is refreshed in materialized view.

Tags: