Help - Search - Members - Calendar
Full Version: Setting intervals for Materialized Views
Oracle DBA Forums > Oracle > Oracle Forum
SickOfOracle
Mar 4 2010, 09:40 AM
I have a script that builds several views and then sets the intervals. The views take a while to build and the users need them in sync so the script builds them with a refresh interval of one day then when they're all done, sets the interval to every few minutes ('SYSDATE + 1/1440').

I have the code in a script. Builds several related views, sets the intervals, builds more related views, sets interval ... If I pull out the sql code and run it with SQL plus, everything works. When I run the script using OEM, the views are built but the intervals are never set. Any idea how to get the intervals set through OEM?

Script below


#!/bin/ksh

ORACLE_SID=ACESPRD
export ORACLE_SID
ORACLE_HOME=/apps/oracle/product/11.1
export ORACLE_HOME
#PATH=$ORACLE_HOME/bin:$PATH
#export PATH

$ORACLE_HOME/bin/sqlplus -s /nolog << EOF

connect aces/******@acesprd;

DROP MATERIALIZED VIEW ACES.mv_registry;
DROP MATERIALIZED VIEW ACES.MV_REGISTRY_E;

create materialized view
aces.mv_registry
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_registry"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_registry"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_registry"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_registry"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_registry"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_registry"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_registry"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_registry"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_registry"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_registry"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_registry"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_registry"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_registry"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_registry"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_registry"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_registry"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_registry"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_registry"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_registry"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_registry"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_registry"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_registry"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_registry"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_registry"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_registry"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_registry"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_registry"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_registry"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_registry"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_registry"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_registry"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_registry"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_registry"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_registry"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_registry"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_registry"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_registry"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_registry"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_registry"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_registry"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_registry"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_registry"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_registry"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_registry"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_registry"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_registry"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_registry"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_registry"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_registry"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_registry"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_registry"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_registry"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_registry"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select *
from aces_mvw.mv_registry@aces_cdb);

CREATE INDEX ACES.IDX_mv_registry ON ACES.mv_registry ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_REGISTRY"
ADD (CONSTRAINT "MV_REGISTRY_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM")
USING INDEX
TABLESPACE "ACES_TS");

CREATE INDEX "ACES"."ALT_IDX_MV_REGISTRY"
ON "ACES"."MV_REGISTRY" ("STATUS_CODE", "COUNTY_CODE",
"TOWNSHIP_CODE", "CMI")
TABLESPACE "ACES_TS" LOCAL (PARTITION "AL_mv_registry" ,
PARTITION "AK_mv_registry" ,
PARTITION "AZ_mv_registry" ,
PARTITION "AR_mv_registry" ,
PARTITION "CA_mv_registry" ,
PARTITION "CO_mv_registry" ,
PARTITION "CT_mv_registry" ,
PARTITION "DE_mv_registry" ,
PARTITION "DC_mv_registry" ,
PARTITION "FL_mv_registry" ,
PARTITION "GA_mv_registry" ,
PARTITION "HI_mv_registry" ,
PARTITION "ID_mv_registry" ,
PARTITION "IL_mv_registry" ,
PARTITION "IN_mv_registry" ,
PARTITION "IA_mv_registry" ,
PARTITION "KS_mv_registry" ,
PARTITION "KY_mv_registry" ,
PARTITION "LA_mv_registry" ,
PARTITION "ME_mv_registry" ,
PARTITION "MD_mv_registry" ,
PARTITION "MA_mv_registry" ,
PARTITION "MI_mv_registry" ,
PARTITION "MN_mv_registry" ,
PARTITION "MS_mv_registry" ,
PARTITION "MO_mv_registry" ,
PARTITION "MT_mv_registry" ,
PARTITION "NE_mv_registry" ,
PARTITION "NV_mv_registry" ,
PARTITION "NH_mv_registry" ,
PARTITION "NJ_mv_registry" ,
PARTITION "NM_mv_registry" ,
PARTITION "NY_mv_registry" ,
PARTITION "NC_mv_registry" ,
PARTITION "ND_mv_registry" ,
PARTITION "OH_mv_registry" ,
PARTITION "OK_mv_registry" ,
PARTITION "OR_mv_registry" ,
PARTITION "PA_mv_registry" ,
PARTITION "RI_mv_registry" ,
PARTITION "SC_mv_registry" ,
PARTITION "SD_mv_registry" ,
PARTITION "TN_mv_registry" ,
PARTITION "TX_mv_registry" ,
PARTITION "UT_mv_registry" ,
PARTITION "VT_mv_registry" ,
PARTITION "VA_mv_registry" ,
PARTITION "WA_mv_registry" ,
PARTITION "WV_mv_registry" ,
PARTITION "WI_mv_registry" ,
PARTITION "WY_mv_registry" ,
PARTITION "PR_mv_registry" ,
PARTITION "VI_mv_registry" );

create materialized view
aces.mv_registry_e
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_registry_E"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_registry_E"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_registry_E"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_registry_E"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_registry_E"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_registry_E"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_registry_E"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_registry_E"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_registry_E"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_registry_E"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_registry_E"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_registry_E"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_registry_E"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_registry_E"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_registry_E"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_registry_E"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_registry_E"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_registry_E"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_registry_E"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_registry_E"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_registry_E"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_registry_E"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_registry_E"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_registry_E"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_registry_E"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_registry_E"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_registry_E"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_registry_E"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_registry_E"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_registry_E"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_registry_E"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_registry_E"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_registry_E"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_registry_E"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_registry_E"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_registry_E"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_registry_E"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_registry_E"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_registry_E"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_registry_E"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_registry_E"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_registry_E"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_registry_E"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_registry_E"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_registry_E"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_registry_E"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_registry_E"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_registry_E"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_registry_E"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_registry_E"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_registry_E"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_registry_E"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_registry_E"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select
state_code,
rept_num,
county_code,
township_code,
report_with,
sample_code,
naics_code,
ownership_code,
parent_child_code,
ui_number,
selected_ui_number,
selected_ui_run,
selection_size,
proration_indicator,
selection_weight,
bmk_weight,
state_use
from aces_mvw.mv_registry@aces_cdb);

CREATE INDEX ACES.IDX_mv_registry_e ON ACES.mv_registry_e ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_REGISTRY_E"
ADD (CONSTRAINT "MV_REGISTRY_E_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM", "COUNTY_CODE", "TOWNSHIP_CODE",
"NAICS_CODE", "OWNERSHIP_CODE")
USING INDEX
TABLESPACE "ACES_TS");

DECLARE
X NUMBER;
Y NUMBER;
BEGIN
SELECT JOB
INTO X
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_REGISTRY"%';

SELECT JOB
INTO Y
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_REGISTRY_E"%';

DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);
DBMS_JOB.INTERVAL(X,'SYSDATE + 1/1440');
DBMS_JOB.INTERVAL(Y,'SYSDATE + 1/1440');
DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);

exception
when others then
HTML_EMAIL('xxxx@verizon.net',
'mv creation error',
'mv creation error',
'<table><tr><td>'||SQLCODE||'</td><td>'||SQLERRM||'</td></tr></table>');
END;
/

DROP MATERIALIZED VIEW ACES.MV_ADDRESS;

CREATE MATERIALIZED VIEW
ACES.MV_ADDRESS
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_MV_ADDRESS"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_MV_ADDRESS"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_MV_ADDRESS"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_MV_ADDRESS"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_MV_ADDRESS"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_MV_ADDRESS"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_MV_ADDRESS"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_MV_ADDRESS"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_MV_ADDRESS"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_MV_ADDRESS"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_MV_ADDRESS"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_MV_ADDRESS"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_MV_ADDRESS"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_MV_ADDRESS"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_MV_ADDRESS"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_MV_ADDRESS"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_MV_ADDRESS"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_MV_ADDRESS"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_MV_ADDRESS"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_MV_ADDRESS"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_MV_ADDRESS"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_MV_ADDRESS"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_MV_ADDRESS"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_MV_ADDRESS"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_MV_ADDRESS"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_MV_ADDRESS"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_MV_ADDRESS"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_MV_ADDRESS"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_MV_ADDRESS"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_MV_ADDRESS"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_MV_ADDRESS"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_MV_ADDRESS"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_MV_ADDRESS"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_MV_ADDRESS"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_MV_ADDRESS"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_MV_ADDRESS"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_MV_ADDRESS"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_MV_ADDRESS"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_MV_ADDRESS"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_MV_ADDRESS"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_MV_ADDRESS"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_MV_ADDRESS"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_MV_ADDRESS"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_MV_ADDRESS"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_MV_ADDRESS"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_MV_ADDRESS"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_MV_ADDRESS"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_MV_ADDRESS"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_MV_ADDRESS"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_MV_ADDRESS"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_MV_ADDRESS"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_MV_ADDRESS"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_MV_ADDRESS"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
TABLESPACE ACES_TS
using no index
REFRESH FAST NEXT SYSDATE + 1
AS (SELECT * FROM ACES_MVW.MV_ADDRESS@ACES_CDB);

CREATE INDEX ACES.IDX_MV_ADDRESS ON ACES.MV_ADDRESS ("STATE_CODE") LOCAL;

ALTER TABLE ACES.MV_ADDRESS
ADD (CONSTRAINT MV_ADDRESS_PK PRIMARY KEY(STATE_CODE, REPT_NUM, ADDRTYPE) USING INDEX
TABLESPACE ACES_TS);

DECLARE
X NUMBER;
BEGIN
SELECT JOB
INTO X
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_ADDRESS"%';

DBMS_JOB.RUN(X);
DBMS_JOB.INTERVAL(X,'SYSDATE + 1/1440');
DBMS_JOB.RUN(X);

exception
when others then
HTML_EMAIL('xxxx@verizon.net',
'mv creation error',
'mv creation error',
'<table><tr><td>'||SQLCODE||'</td><td>'||SQLERRM||'</td></tr></table>');
END;
/


DROP MATERIALIZED VIEW aces.mv_reporter;
DROP MATERIALIZED VIEW ACES.mv_reporter_e;

create materialized view
aces.mv_reporter
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_reporter"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_reporter"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_reporter"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_reporter"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_reporter"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_reporter"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_reporter"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_reporter"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_reporter"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_reporter"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_reporter"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_reporter"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_reporter"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_reporter"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_reporter"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_reporter"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_reporter"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_reporter"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_reporter"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_reporter"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_reporter"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_reporter"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_reporter"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_reporter"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_reporter"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_reporter"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_reporter"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_reporter"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_reporter"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_reporter"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_reporter"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_reporter"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_reporter"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_reporter"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_reporter"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_reporter"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_reporter"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_reporter"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_reporter"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_reporter"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_reporter"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_reporter"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_reporter"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_reporter"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_reporter"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_reporter"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_reporter"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_reporter"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_reporter"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_reporter"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_reporter"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_reporter"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_reporter"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select *
from aces_mvw.mv_reporter@aces_cdb
where ref_yy||ref_mm >= '200706');

CREATE INDEX ACES.IDX_mv_reporter ON aces.mv_reporter ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_REPORTER"
ADD (CONSTRAINT "MV_REPORTER_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM", "REF_YY", "REF_MM", "PHASE",
"VERSION", "PAYROLL_INDICATOR")
USING INDEX
TABLESPACE "ACES_TS");


create materialized view
aces.mv_reporter_e
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_reporter_E"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_reporter_E"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_reporter_E"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_reporter_E"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_reporter_E"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_reporter_E"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_reporter_E"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_reporter_E"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_reporter_E"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_reporter_E"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_reporter_E"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_reporter_E"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_reporter_E"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_reporter_E"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_reporter_E"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_reporter_E"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_reporter_E"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_reporter_E"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_reporter_E"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_reporter_E"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_reporter_E"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_reporter_E"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_reporter_E"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_reporter_E"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_reporter_E"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_reporter_E"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_reporter_E"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_reporter_E"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_reporter_E"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_reporter_E"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_reporter_E"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_reporter_E"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_reporter_E"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_reporter_E"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_reporter_E"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_reporter_E"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_reporter_E"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_reporter_E"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_reporter_E"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_reporter_E"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_reporter_E"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_reporter_E"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_reporter_E"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_reporter_E"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_reporter_E"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_reporter_E"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_reporter_E"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_reporter_E"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_reporter_E"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_reporter_E"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_reporter_E"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_reporter_E"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_reporter_E"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
com_code1,
com_code2,
pro_factor
from aces_mvw.mv_reporter@aces_cdb
where payroll_indicator = 0
and ref_yy||ref_mm >= '200812');

CREATE INDEX ACES.IDX_MV_REPORTER_E ON ACES.MV_REPORTER_E ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_REPORTER_E"
ADD (CONSTRAINT "MV_REPORTER_E_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM", "REF_YY", "REF_MM", "PHASE",
"VERSION", "PAYROLL_INDICATOR")
USING INDEX
TABLESPACE "ACES_TS");

DECLARE
X NUMBER;
Y NUMBER;
BEGIN
SELECT JOB
INTO X
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_REPORTER"%';

SELECT JOB
INTO Y
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_REPORTER_E"%';

DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);
DBMS_JOB.INTERVAL(X,'SYSDATE + 1/1440');
DBMS_JOB.INTERVAL(Y,'SYSDATE + 1/1440');
DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);

exception
when others then
HTML_EMAIL('xxxx@verizon.net',
'mv creation error',
'mv creation error',
'<table><tr><td>'||SQLCODE||'</td><td>'||SQLERRM||'</td></tr></table>');
END;
/

DROP MATERIALIZED VIEW ACES.MV_MICRODATA;
DROP MATERIALIZED VIEW ACES.MV_MICRODATA_E;

create materialized view
aces.mv_microdata
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_microdata"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_microdata"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_microdata"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_microdata"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_microdata"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_microdata"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_microdata"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_microdata"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_microdata"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_microdata"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_microdata"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_microdata"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_microdata"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_microdata"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_microdata"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_microdata"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_microdata"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_microdata"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_microdata"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_microdata"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_microdata"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_microdata"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_microdata"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_microdata"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_microdata"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_microdata"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_microdata"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_microdata"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_microdata"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_microdata"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_microdata"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_microdata"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_microdata"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_microdata"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_microdata"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_microdata"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_microdata"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_microdata"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_microdata"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_microdata"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_microdata"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_microdata"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_microdata"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_microdata"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_microdata"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_microdata"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_microdata"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_microdata"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_microdata"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_microdata"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_microdata"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_microdata"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_microdata"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select *
from aces_mvw.MV_MICRODATA@ACES_CDB
where ref_yy||ref_mm >= '200706');

CREATE INDEX ACES.IDX_MV_MICRODATA ON ACES.MV_MICRODATA ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_MICRODATA"
ADD (CONSTRAINT "MV_MICRODATA_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM", "REF_YY", "REF_MM", "PHASE",
"VERSION", "DATATYPE", "PAYROLL_INDICATOR")
USING INDEX
TABLESPACE "ACES_TS");

create materialized view
aces.mv_microdata_e
PARTITION BY RANGE ("STATE_CODE")
(PARTITION "AL_mv_microdata_E"
VALUES LESS THAN ('02') TABLESPACE "ACES_AL" ,
PARTITION "AK_mv_microdata_E"
VALUES LESS THAN ('03') TABLESPACE "ACES_AK" ,
PARTITION "AZ_mv_microdata_E"
VALUES LESS THAN ('05') TABLESPACE "ACES_AZ" ,
PARTITION "AR_mv_microdata_E"
VALUES LESS THAN ('06') TABLESPACE "ACES_AR" ,
PARTITION "CA_mv_microdata_E"
VALUES LESS THAN ('07') TABLESPACE "ACES_CA" ,
PARTITION "CO_mv_microdata_E"
VALUES LESS THAN ('09') TABLESPACE "ACES_CO" ,
PARTITION "CT_mv_microdata_E"
VALUES LESS THAN ('10') TABLESPACE "ACES_CT" ,
PARTITION "DE_mv_microdata_E"
VALUES LESS THAN ('11') TABLESPACE "ACES_DE" ,
PARTITION "DC_mv_microdata_E"
VALUES LESS THAN ('12') TABLESPACE "ACES_DC" ,
PARTITION "FL_mv_microdata_E"
VALUES LESS THAN ('13') TABLESPACE "ACES_FL" ,
PARTITION "GA_mv_microdata_E"
VALUES LESS THAN ('14') TABLESPACE "ACES_GA" ,
PARTITION "HI_mv_microdata_E"
VALUES LESS THAN ('16') TABLESPACE "ACES_HI" ,
PARTITION "ID_mv_microdata_E"
VALUES LESS THAN ('17') TABLESPACE "ACES_ID" ,
PARTITION "IL_mv_microdata_E"
VALUES LESS THAN ('18') TABLESPACE "ACES_IL" ,
PARTITION "IN_mv_microdata_E"
VALUES LESS THAN ('19') TABLESPACE "ACES_IN" ,
PARTITION "IA_mv_microdata_E"
VALUES LESS THAN ('20') TABLESPACE "ACES_IA" ,
PARTITION "KS_mv_microdata_E"
VALUES LESS THAN ('21') TABLESPACE "ACES_KS" ,
PARTITION "KY_mv_microdata_E"
VALUES LESS THAN ('22') TABLESPACE "ACES_KY" ,
PARTITION "LA_mv_microdata_E"
VALUES LESS THAN ('23') TABLESPACE "ACES_LA" ,
PARTITION "ME_mv_microdata_E"
VALUES LESS THAN ('24') TABLESPACE "ACES_ME" ,
PARTITION "MD_mv_microdata_E"
VALUES LESS THAN ('25') TABLESPACE "ACES_MD" ,
PARTITION "MA_mv_microdata_E"
VALUES LESS THAN ('26') TABLESPACE "ACES_MA" ,
PARTITION "MI_mv_microdata_E"
VALUES LESS THAN ('27') TABLESPACE "ACES_MI" ,
PARTITION "MN_mv_microdata_E"
VALUES LESS THAN ('28') TABLESPACE "ACES_MN" ,
PARTITION "MS_mv_microdata_E"
VALUES LESS THAN ('29') TABLESPACE "ACES_MS" ,
PARTITION "MO_mv_microdata_E"
VALUES LESS THAN ('30') TABLESPACE "ACES_MO" ,
PARTITION "MT_mv_microdata_E"
VALUES LESS THAN ('31') TABLESPACE "ACES_MT" ,
PARTITION "NE_mv_microdata_E"
VALUES LESS THAN ('32') TABLESPACE "ACES_NE" ,
PARTITION "NV_mv_microdata_E"
VALUES LESS THAN ('33') TABLESPACE "ACES_NV" ,
PARTITION "NH_mv_microdata_E"
VALUES LESS THAN ('34') TABLESPACE "ACES_NH" ,
PARTITION "NJ_mv_microdata_E"
VALUES LESS THAN ('35') TABLESPACE "ACES_NJ" ,
PARTITION "NM_mv_microdata_E"
VALUES LESS THAN ('36') TABLESPACE "ACES_NM" ,
PARTITION "NY_mv_microdata_E"
VALUES LESS THAN ('37') TABLESPACE "ACES_NY" ,
PARTITION "NC_mv_microdata_E"
VALUES LESS THAN ('38') TABLESPACE "ACES_NC" ,
PARTITION "ND_mv_microdata_E"
VALUES LESS THAN ('39') TABLESPACE "ACES_ND" ,
PARTITION "OH_mv_microdata_E"
VALUES LESS THAN ('40') TABLESPACE "ACES_OH" ,
PARTITION "OK_mv_microdata_E"
VALUES LESS THAN ('41') TABLESPACE "ACES_OK" ,
PARTITION "OR_mv_microdata_E"
VALUES LESS THAN ('42') TABLESPACE "ACES_OR" ,
PARTITION "PA_mv_microdata_E"
VALUES LESS THAN ('43') TABLESPACE "ACES_PA" ,
PARTITION "RI_mv_microdata_E"
VALUES LESS THAN ('45') TABLESPACE "ACES_RI" ,
PARTITION "SC_mv_microdata_E"
VALUES LESS THAN ('46') TABLESPACE "ACES_SC" ,
PARTITION "SD_mv_microdata_E"
VALUES LESS THAN ('47') TABLESPACE "ACES_SD" ,
PARTITION "TN_mv_microdata_E"
VALUES LESS THAN ('48') TABLESPACE "ACES_TN" ,
PARTITION "TX_mv_microdata_E"
VALUES LESS THAN ('49') TABLESPACE "ACES_TX" ,
PARTITION "UT_mv_microdata_E"
VALUES LESS THAN ('50') TABLESPACE "ACES_UT" ,
PARTITION "VT_mv_microdata_E"
VALUES LESS THAN ('51') TABLESPACE "ACES_VT" ,
PARTITION "VA_mv_microdata_E"
VALUES LESS THAN ('52') TABLESPACE "ACES_VA" ,
PARTITION "WA_mv_microdata_E"
VALUES LESS THAN ('54') TABLESPACE "ACES_WA" ,
PARTITION "WV_mv_microdata_E"
VALUES LESS THAN ('55') TABLESPACE "ACES_WV" ,
PARTITION "WI_mv_microdata_E"
VALUES LESS THAN ('56') TABLESPACE "ACES_WI" ,
PARTITION "WY_mv_microdata_E"
VALUES LESS THAN ('57') TABLESPACE "ACES_WY" ,
PARTITION "PR_mv_microdata_E"
VALUES LESS THAN ('73') TABLESPACE "ACES_PR" ,
PARTITION "VI_mv_microdata_E"
VALUES LESS THAN ('79') TABLESPACE "ACES_VI")
tablespace aces_ts
using no index
REFRESH FAST NEXT SYSDATE + 1
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
datatype,
data_value,
deeps_flag,
state_flag
from ACES_MVW.MV_MICRODATA@aces_cdb
where payroll_indicator = 0
and ref_yy||ref_mm >= '200812');

CREATE INDEX ACES.IDX_MV_MICRODATA_E ON ACES.MV_MICRODATA_E ("STATE_CODE") LOCAL;

ALTER TABLE "ACES"."MV_MICRODATA_E"
ADD (CONSTRAINT "MV_MICRODATA_E_PK" PRIMARY
KEY("STATE_CODE", "REPT_NUM", "REF_YY", "REF_MM", "PHASE",
"VERSION", "DATATYPE", "PAYROLL_INDICATOR")
USING INDEX
TABLESPACE "ACES_TS");

DECLARE
X NUMBER;
Y NUMBER;
BEGIN
SELECT JOB
INTO X
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_MICRODATA"%';

SELECT JOB
INTO Y
FROM USER_JOBS
WHERE WHAT LIKE '%"MV_MICRODATA_E"%';

DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);
DBMS_JOB.INTERVAL(X,'SYSDATE + 1/1440');
DBMS_JOB.INTERVAL(Y,'SYSDATE + 1/1440');
DBMS_JOB.RUN(X);
DBMS_JOB.RUN(Y);

exception
when others then
HTML_EMAIL(xxxx@verizon.net',
'mv creation error',
'mv creation error',
'<table><tr><td>'||SQLCODE||'</td><td>'||SQLERRM||'</td></tr></table>');
END;
/

EOF
mrculp
Mar 4 2010, 11:12 AM
I don't know that I would use EM for this. I would crontab the job and run it through there, because if for some reason EM is not working properly you can still at least run your jobs.
JGarmany
Mar 10 2010, 12:27 PM
Why the two step process?
The refresh is not set until the mview is created so it will not try and refresh during the create, just as the next refresh is not scheduled until the current refresh completes.

This is not scheduled in OEM, you are just running it in OEM correct?
If OEM does not run the script correctly, run it from SQL*Plus.

http://www.dba-oracle.com/t_materialized_v...performance.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2013 Invision Power Services, Inc.