Materialized view

=======>Step1  for dropping the materialized view

drop materialized view MV_SUB_MASTER;

=======>Step2:  for dropping the exiting mv refresh interval

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.DAILY_REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.REGULAR_REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.HOURLY_REFRESH’);
END;
/

=======>Step3: for creating the mv

CREATE or replace MATERIALIZED VIEW MV_SUB_MASTER
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT WIMAX_ID,TEMPLATE_ID,PACKAGE_NAME,PAYTYPE,STATUS_CODE_V,EMAIL,
PHONE,START_DATE,EXPIRY_DATE,FIRST_NAME,BANDWIDTH,WIMAX_ID as CUI
FROM MV_SUB_MASTER@post2p;

=======>Step4: configure the refresh interval of mv

BEGIN
Dbms_Refresh.Make(
name => ‘scott.DAILY_REFRESH’,
list => ”,
next_date => SYSDATE,
interval => ‘SYSDATE+1260/1440’,
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

SELECT TO_CHAR(sysdate+360/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(SYSDATE+1260/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(trunc(SYSDATE+1)+1260/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
select TO_CHAR(SYSDATE, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(SYSDATE+1, ‘dd-mm-yy hh24:mi:ss’) from dual;

=======>Step5:

BEGIN
Dbms_Refresh.Add(
name => ‘scott.DAILY_REFRESH’,
list => ‘scott.MV_SUB_MASTER’,
lax => TRUE);
END;
/
select failures from user_jobs;

=======>Step6:  Create Index on WIMAX_ID

CREATE INDEX IDX_WIMAXID ON MV_SUB_MASTER(WIMAX_ID);

select * from sys.indexes where object_id = (select object_id from sys.objects where name = ‘IDX_WIMAXID’);

==============================================================================================================
============================ NEW WAY OF CREATING MV AND UPDATE IT AUTOMATICALLY ==============================

CREATE MATERIALIZED VIEW MV_SUB_MASTER
refresh
complete
next sysdate+360/1440
AS
SELECT WIMAX_ID,TEMPLATE_ID,PACKAGE_NAME,PAYTYPE,STATUS_CODE_V,EMAIL,PHONE,START_DATE,EXPIRY_DATE,FIRST_NAME,BANDWIDTH,WIMAX_ID as CUI FROM MV_SUB_MASTER@post2p;

select count(*) from mv_sub_master;
select count(*) from mv_sub_master@post2p;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.