Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Gary Graham-Oracle

Just using SQLcl, probably not.  Perhaps using nested scripts, each with a REPEAT command (sleep limit is 120 seconds only), might be a possibility.  Why not checkout Windows Task Scheduler to launch SQLcl sessions instead:

https://en.wikipedia.org/wiki/Windows_Task_Scheduler

1 person found this helpful
tbhluehorn

This sounds like where I want to go with this.   I tried downloading the lastest version of SqlCl, but now having trouble connecting it to the database I connect to via SQL Developer. Please help me connect to the database using SqLCl I work with on the same workstation using SQL Developer.    I think that would be the first step ?    Then would it be possible to schedule using task scheduler.

tbhluehorn

I am replying to my own post. I was able to connect to the oracle database using SQLCL. I was able able to execute the script I needed which I was not able to execute in sqlplus. Now I am going to try to schedule this using windows task scheduler. I was having trouble connecting to the database earlier, when I downloaded SqlCL and extracted it to a folder on my local drive(I couldn't connect to the databases). I came across another post somewhere on the net which indicated one can use the SqlCL file which resides in the same install directory as sqldeveloper. I looked in my current sql developer installation directory and found this file sql.bat in the bin directory. From here I entered my user name/password and database name as prompted and was able to execute a previously saved script.sql file which outputted to a text file using spool. pastedImage_0.png

Gary Graham-Oracle

Glad you worked it out, but you should be able to connect to your database using a standalone SQLcl install or the one that comes with SQL Developer.  There may be one or more differences when running sql.exe and sql.bat.  One such difference is described in another discussion, sqlcl java errors under different db homes which I repeat here for convenience...

1. If a JRE is copied into a sqlcl\jre folder, then SQLcl uses that Java version.
2. Otherwise, running sql.bat uses an explicitly set JAVA_HOME, followed by the Java version appearing first in PATH.
3. Otherwise, running sql.exe first checks the Windows Registry for which Java version to use, then falls through to the rules in (2).
Java version must be at least 1.7 to avoid an Unsupported major.minor version error.

If you tried to connect using a TNS alias, then the exe will check the Windows registry for the location of tnsnames.ora whereas the bat file relies on an environment variable being set, either ORACLE_HOME or TNS_ALIAS.

Good luck moving forward and getting your script executed via Windows Task Scheduler.

castorp

2. Otherwise, running sql.bat uses an explicitly set JAVA_HOME, followed by the Java version appearing first in PATH.

3. Otherwise, running sql.exe first checks the Windows Registry for which Java version to use, then falls through to the rules in (2).

Java version must be at least 1.7 to avoid an Unsupported major.minor version error.

The "fall through to (2)" does not work for me even though I have a JAVA_HOME and java.exe is in the path:

c:\etc\sqlcl\bin>echo %JAVA_HOME% C:\etc\Java8  c:\etc\sqlcl\bin>java -version java version "1.8.0_144" Java(TM) SE Runtime Environment (build 1.8.0_144-b01) Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)  c:\etc\sqlcl\bin>sql.exe This application requires a Java Runtime Environment 1.8.0_50  c:\etc\sqlcl\bin>

sql.exe then just opens the browser with java.com.

sql.bat works fine though which is a workaround for now.

I do have Java 8 and Java 9 installed though. I am not sure which registry entry sql.exe is checking.

Gary Graham-Oracle 
I do have Java 8 and Java 9 installed though. I am not sure which registry entry sql.exe is checking.

When installing a new JDK, I never install the public JRE that comes with it.  This is so any Java applications for which I do not need to run with a specific JDK will use the latest JRE that Oracle's Java Update pushes out, which of course always lags the latest, greatest, possibly unstable stuff. 

I believe the last JRE push was for Java 8 update 151, but at some point Java Update will switch to pushing out Java 9 updates.

Possibly sql.exe finds Java 9 in the registry and is complaining about that but giving a misleading message about Java 8 update 50 being the minimum required version.  If Java 9 is copied into a sqlcl\jre folder (I tried this with SQLcl 17.3), then SQLcl (sql.exe) uses Java 9 with no complaints.  Once SQLcl opens, you can "show java" to see which Java is running.

castorp 
Gary Graham-Oracle wrote:
When installing a new JDK, I never install the public JRE that comes with it. This is so any Java applications for which I do not need to run with a specific JDK will use the latest JRE that Oracle's Java Update pushes out, which of course always lags the latest, greatest, possibly unstable stuff.

I don't do that (mainly because the "public JRE" also installed that dreaded browser applet plugin for older versions). I have only the JDKs installed, no "public JRE"

Possibly sql.exe finds Java 9 in the registry and is complaining about that but giving a misleading message about Java 8 update 50 being the minimum required version. If Java 9 is copied into a sqlcl\jre folder (I tried this with SQLcl 17.3), then SQLcl (sql.exe) uses Java 9 with no complaints. Once SQLcl opens, you can "show java" to see which Java is running.

I would like to avoid copying a whole JRE around if possible.

The question is: why doesn't it fall back to using JAVA_HOME if the found Java installation isn't "acceptable"?

Isn't there some "--java-home" or "--jre-location" parameter for sql.exe? And

I looked at the Launch4J documentation, but apparently the JDK/JRE can't be specified for that through an INI file.

Gary Graham-Oracle

When the sql.exe is built by Launch4J, we pass it a chunk of XML to control certain things as described in...

Launch4j - Cross-platform Java executable wrapper

in the Configuration file section.  There are a couple of different files in our code base with such XML, so I am not sure which is actually passed in, but here is one version of how the JRE may be configured...

  <jre>
    <path></path>
    <bundledJre64Bit>false</bundledJre64Bit>
    <bundledJreAsFallback>false</bundledJreAsFallback>
    <minVersion>1.8.0_111</minVersion>
    <maxVersion></maxVersion>
    <jdkPreference>preferJre</jdkPreference>
    <runtimeBits>64/32</runtimeBits>
    <opt>-Djava.awt.headless=true</opt>
    <opt>-Dapple.awt.UIElement=true</opt>
    <opt>-Dstartup.directory=%OLD_PWD%</opt>
    <opt>-Ddbtools.windows_proxy_enabled=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable%</opt>
    <opt>-Ddbtools.windows_proxy_server=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer%</opt>
    <opt>-Ddbtools.windows_proxy_pac=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\AutoConfigURL%</opt>
    <opt>-Ddbtools.windows_proxy_override=%HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyOverride%</opt>
  </jre>

So you see there is no bundled JRE specified, no maximum version to search for, only a minimum version.  Not sure where Launch4J searches in the Windows registry (or elsewhere) or why it is not falling through to JAVA_HOME in your environment.  At least with sql.bat you can read the code and see what is does.

1 - 8

Post Details

Added on Feb 7 2022
#database
12 comments
558 views
3 mentions