Skip to Main Content

SQL & PL/SQL

Why my mview is not refreshing after commit?

JackKFeb 7 2022 — edited Feb 7 2022

Hi All.
I have tables:

create table OS_OUTAGES (
 out_id       NUMBER(22) generated by default on null as identity (nocache),
 out_number     VARCHAR2(40) not null,
 out_status     VARCHAR2(10) not null,
 out_dscr      VARCHAR2(4000),
 out_is_proposition VARCHAR2(1),
 out_usr_id     INTEGER not null,
 out_oki_code    VARCHAR2(20) not null,
 out_bup_code    NUMBER(12) not null,
 out_oup_id     NUMBER(20),
 out_gel_mrid    VARCHAR2(70) not null,
 out_vl_mrid    VARCHAR2(70),
 out_oty_code    VARCHAR2(20) not null,
 out_ouv_id     NUMBER(22) not null,
 out_swl_id     NUMBER(4),
 out_locked_by   NUMBER(22),
 out_locked_until  DATE,
 out_out_id     NUMBER(22),
 out_sch_id     NUMBER(22),
 out_ois_id     NUMBER(22),
 out_number__sort  as ("OMSW"."OS_UTIL"."OBJNUMBER2SORT"("OUT_NUMBER")),
 out_root_out_id  NUMBER(22),
 out_usid      VARCHAR2(55) invisible
);

alter table OS_OUTAGES add constraint OUT_PK primary key (OUT_ID);

alter table OS_OUTAGES
 add constraint OUT_OUV_FK foreign key (OUT_OUV_ID)
 references OS_OUTAGE_VERSIONS (OUV_ID) on delete set null
 deferrable initially deferred;

---

create table OS_OUTAGE_VERSIONS (
 ouv_id           NUMBER(22) generated by default on null as identity (nocache),
 ouv_change_time       DATE default sysdate not null,
 ouv_start          TIMESTAMP(0) WITH TIME ZONE not null,
 ouv_end           TIMESTAMP(0) WITH TIME ZONE not null,
 ouv_readiness        NUMBER,
 ouv_cycle          VARCHAR2(2) not null,
 ouv_break_possibility    CHAR(1),
 ouv_night_work       CHAR(1),
 ouv_saturday_work      CHAR(1),
 ouv_sunday_work       CHAR(1),
 ouv_out_id         NUMBER(22) not null,
 ouv_usr_id         INTEGER not null,
 ouv_status         VARCHAR2(10),
 ouv_version         VARCHAR2(43) not null,
 ouv_ouv_id         NUMBER(22),
 ouv_readiness_u       VARCHAR2(1),
 ouv_required_time      NUMBER,
 ouv_required_time_u     VARCHAR2(1),
 ouv_required_time__sort   as ("OUV_REQUIRED_TIME"*DECODE("OUV_REQUIRED_TIME_U",'d',1440,'h',60,'m',1)),
 ouv_prefered_start     TIMESTAMP(0) WITH TIME ZONE,
 ouv_prefered_end      TIMESTAMP(0) WITH TIME ZONE,
 ouv_cost          NUMBER,
 ouv_refusing_cost      NUMBER,
 ouv_readiness__sort     as ("OUV_READINESS"*DECODE("OUV_READINESS_U",'d',1440,'h',60,'m',1)),
 ouv_crr_id         NUMBER(4),
 ouv_rb2_09_valid      CHAR(1) default 'n' not null,
 ouv_in_cartesian      CHAR(1) default 'y' not null,
 ouv_weekend_work      CHAR(1),
 ouv_holiday_work      CHAR(1),
 ouv_priority        NUMBER(1) default 1,
 ouv_night_readiness     NUMBER(3),
 ouv_night_readiness_u    CHAR(1),
 ouv_busbar_work_possibility CHAR(1),
 ouv_schedule_required    CHAR(1),
 ouv_programme_required   CHAR(1),
 ouv_temp_ver        NUMBER(3),
 ouv_length         as ("OMSW"."OS_OUTAGE"."OUV_LENGTH_CALC"("OUV_START","OUV_END"))
);

alter table OS_OUTAGE_VERSIONS add constraint OUV_PK primary key (OUV_ID);

alter table OS_OUTAGE_VERSIONS
 add constraint OUV_OUT_FK foreign key (OUV_OUT_ID)
 references OS_OUTAGES (OUT_ID) on delete cascade;

---

create table OS_OUTAGE_PACKAGES (
 opa_id      NUMBER(22) generated by default on null as identity (nocache),
 opa_usr_id    NUMBER(22) not null,
 opa_opv_id    NUMBER(22),
 opa_locked_by  NUMBER(22),
 opa_locked_until DATE,
 opa_status    VARCHAR2(5) not null,
 opa_opa_id    NUMBER(22),
 opa_number    VARCHAR2(40),
 opa_sch_id    NUMBER(22),
 opa_pis_id    NUMBER(22),
 opa_bup_code   NUMBER(12) not null,
 opa_number__sort as ("OMSW"."OS_UTIL"."OBJNUMBER2SORT"("OPA_NUMBER")),
 opa_root_opa_id NUMBER(22),
 opa_type     CHAR(1) default 'P' not null,
 opa_category   CHAR(1)
);

alter table OS_OUTAGE_PACKAGES add constraint OPA_PK primary key (OPA_ID);

alter table OS_OUTAGE_PACKAGES
 add constraint OPA_OPV_FK foreign key (OPA_OPV_ID)
 references OS_OUTAGE_PACKAGE_VERSIONS (OPV_ID)
 deferrable initially deferred;

---

create table OS_OUTAGE_PACKAGE_VERSIONS (
 opv_id     NUMBER(22) generated by default on null as identity (nocache),
 opv_change_time DATE default sysdate not null,
 opv_opa_id   NUMBER(22) not null,
 opv_opv_id   NUMBER(22),
 opv_crr_id   NUMBER(4),
 opv_usr_id   NUMBER(22) not null,
 opv_version   VARCHAR2(43) not null,
 opv_management VARCHAR2(4),
 opv_pp_demand  CHAR(1),
 opv_h_demand  CHAR(1),
 opv_bw_demand  CHAR(1),
 opv_pca_code  VARCHAR2(10),
 opv_out_id   NUMBER(22),
 opv_temp_ver  NUMBER(3),
 opv_dscr    VARCHAR2(1500)
);

alter table OS_OUTAGE_PACKAGE_VERSIONS add constraint OPV_PK primary key (OPV_ID);

alter table OS_OUTAGE_PACKAGE_VERSIONS
 add constraint OPV_OPA_FK foreign key (OPV_OPA_ID)
 references OS_OUTAGE_PACKAGES (OPA_ID) on delete cascade;

alter table OS_OUTAGE_PACKAGE_VERSIONS
 add constraint OPV_OUT_FK foreign key (OPV_OUT_ID)
 references OS_OUTAGES (OUT_ID);

---

create table OS_OUTS_IN_PACKAGE (
 oip_id   NUMBER(22) generated by default on null as identity (nocache),
 oip_opv_id NUMBER(22) not null,
 oip_ouv_id NUMBER(22) not null,
 oip_status CHAR(1),
 oip_created DATE default sysdate
);

alter table OS_OUTS_IN_PACKAGE
 add constraint OIP_OPV_FK foreign key (OIP_OPV_ID)
 references OS_OUTAGE_PACKAGE_VERSIONS (OPV_ID) on delete cascade;

alter table OS_OUTS_IN_PACKAGE
 add constraint OIP_OUV_FK foreign key (OIP_OUV_ID)
 references OS_OUTAGE_VERSIONS (OUV_ID) on delete cascade;

There are of course some indexes created on FK columns.
I create materialized view log created:

create materialized view log on OS_OUTAGES
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
out_number
,out_status
,out_dscr
,out_is_proposition
,out_usr_id
,out_oki_code
,out_bup_code
,out_gel_mrid
,out_vl_mrid
,out_oty_code
,out_ouv_id
,out_swl_id
,out_locked_by
,out_locked_until
,out_out_id
,out_sch_id
,out_ois_id
--out_number__sort
,out_root_out_id
--out_usid
) including new values;

create materialized view log on OS_OUTAGE_VERSIONS
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--ouv_id
ouv_change_time
,ouv_start
,ouv_end
,ouv_readiness
,ouv_cycle
,ouv_break_possibility
,ouv_night_work
,ouv_saturday_work
,ouv_sunday_work
,ouv_out_id
,ouv_usr_id
,ouv_status
,ouv_version
,ouv_ouv_id
,ouv_readiness_u
,ouv_required_time
,ouv_required_time_u
--ouv_required_time__sort
,ouv_prefered_start
,ouv_prefered_end
,ouv_cost
,ouv_refusing_cost
--ouv_readiness__sort
,ouv_crr_id
,ouv_rb2_09_valid
,ouv_in_cartesian
,ouv_weekend_work
,ouv_holiday_work
,ouv_priority
,ouv_night_readiness
,ouv_night_readiness_u
,ouv_busbar_work_possibility
,ouv_schedule_required
,ouv_programme_required
,ouv_temp_ver
--ouv_length
) including new values;

create materialized view log on OS_OUTAGE_PACKAGES
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--opa_id
opa_usr_id
,opa_opv_id
,opa_locked_by
,opa_locked_until
,opa_status
,opa_opa_id
,opa_number
,opa_sch_id
,opa_pis_id
,opa_bup_code
--,opa_number__sort
,opa_root_opa_id
,opa_type
,opa_category
) including new values;

create materialized view log on OS_OUTAGE_PACKAGE_VERSIONS
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--opv_id
opv_change_time
,opv_opa_id
,opv_opv_id
,opv_crr_id
,opv_usr_id
,opv_version
,opv_management
,opv_pp_demand
,opv_h_demand
,opv_bw_demand
,opv_pca_code
,opv_out_id
,opv_temp_ver
,opv_dscr
) including new values;

create materialized view log on OS_OUTS_IN_PACKAGE
 with --primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
 oip_id
,oip_opv_id
,oip_ouv_id
,oip_status
,oip_created
) including new values;

Then I create mview:

create materialized view OS_OPV_AGGR_MVW
refresh fast on commit
as
SELECT opa_id, opv_id,
    min(ouv_start) AS min_start,
    max(ouv_end)  AS max_end
   ,count(*)    AS cnt
 FROM OS_OUTAGE_PACKAGES
   ,OS_OUTAGE_PACKAGE_VERSIONS
   ,OS_OUTS_IN_PACKAGE
   ,OS_OUTAGES
   ,OS_OUTAGE_VERSIONS
 WHERE opv_id = opa_opv_id
  AND oip_opv_id = opa_opv_id
  AND out_ouv_id = oip_ouv_id
  AND ouv_id = out_ouv_id
 GROUP BY opa_id, opv_id;

Mview is created correctly. However, when transaction is commited, mview is not being refreshed and rows in mvlogs persist.
I don't know where is the problem.
Could anyone help me?
I am using Oracle 19c.
Best regards,
Jacek

This post has been answered by Jonathan Lewis on Feb 7 2022
Jump to Answer
Comments
Post Details
Added on Feb 7 2022
12 comments
168 views