Oracle EBS: How to Create Materialized View & How to Refresh Materialized View

Subject: We will review here how to create materialized view and how to refresh the materialized view on demand.

 

Step1: Create Employee and Employee Address table based on that we create Materialized view. Also, insert 1 -1 record each.

create table xx_emp (emp_num NUMBER, emp_name VARCHAR2(240));

INSERT INTO xx_emp VALUES(1001,'David');

 

create table xx_emp_add (emp_num NUMBER, address VARCHAR2(240));

INSERT INTO xx_emp_add VALUES(1001,'USA');

 

Step2: Create materialized view based on the above-created custom tables.

 

create materialized view xx_emp_v AS

select

a.emp_num,

a.emp_name,

b.address

from

xx_emp a,

xx_emp_add b

where a.emp_num=b.emp_num;

 

Step3: Try to run the materialized view.

select * from xx_emp_v;  --It will give one record only

                                    

 

Step4: Create a new Entry and check the materialized view output.

INSERT INTO xx_emp VALUES(1002,'John');

INSERT INTO xx_emp_add VALUES(1002,'France');

 

select * from xx_emp_v;  --It will give one record only

 

Step5: We need to refresh the materialized view using the below script and check the Materialized view output.

exec dbms_mview.refresh('xx_emp_v'); -- Materialized view refresh script.

select * from xx_emp_v;  --It will give two records only