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