materialized views

Oracle materialized views

Materialized views are oracle objects those contains results of the query. They are local copies of the objects located remotely or used to create summary table based on the aggregation of a tables's data.

grant from system:
grant create materialized view, query rewrite to SCOTT;

Create matview:
create materialized view meta_table_info_mview
build immediate refresh complete enable query rewrite as select * from meta_table_info;
--Enable query rewrite needs EE and it will fail if database is XE.
create materialized view md_table_info_mview
build immediate refresh complete as select * from meta_table_info;

Create matview logs:
create materialized view log on meta_table_info;

DML on mat view: DML operations are not permitted on mat views.'
delete from meta_table_info_mview where table_name='SKUIPOPARAM'

ORA-01732: data manipulation operation not legal on this view

Refresh matview:
Execute dbms_mview.refresh('MD_TABLE_INFO_MVIEW');


--Query to check all mviews
select * from user_mviews;

--query to check master table and mview log
select MASTER, LOG_TABLE from USER_MVIEW_LOGS

--Find related mview log tables by using following queries
select TABLE_NAME from USER_TABLES where table_name like '%MD_TABLE_INFO%';
 or
select  master, log, temp_log from sys.mlog$ where mowner = 'WWFMGR' and master = 'MD_TABLE_INFO';

--Update a record in master table and check if mview log table updated or not

update MD_TABLE_INFO set table_name='XXX' WHERE TABLE_NAME='SS';

select * from MLOG$_MD_TABLE_INFO;