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

APC
Answer
It's the use of the AUTONOMOUS TRANSACTION pragma. Your current transaction cannot see the result of that DDL because it occurs outside of the current transaction. The clue is in the name.

Of course, you cannot execute the DDL in a trigger without using that pragma, so you're pretty well stymied. There is a solution in 11g but that's not going to help you. Unfortunately, your only option is to pre-create the required partitions ahead of the need. For instance, you could have a DBMS JOB to create a partition for the next month which runs on the last day of each month (or whatever date makes business sense).

Cheers, APC

blog: http://radiofreetooting.blogspot.com
Marked as Answer by 676821 · Sep 27 2020
Laurent Schneider
I second Andrew arguments. In 11g, you have automatic partition creation with interval partitioning.

In 8i, DBMS_JOB rules !

Something like (picture)

table T (partition p2007, partition p2008, partition MAXVALUES)

trigger TR if values > (select max(highvalue) from user_tab_parts) then DBMS_JOB.CREATE_JOB('at midnight, split partition MAXVALUES in MAXVALUES/P2009 ');
and insert row in partition MAXVALUES

HTH
Laurent

Edited by: Laurent Schneider on Jan 14, 2009 3:24 PM
1 person found this helpful
Laurent Schneider
or, much easier and cleaner,

every sunday, check for values in MAXVALUE partition and move those rows in new partitions...
1 person found this helpful
OrionNet
Hello,

You don't need trigger to identify missing partitions and add them, see following procedure

using v_missing_months you can create n number of partitions ahead of time. You can also use this to create partition one month in advance and it will work for any "RANGE" partitioned table. Then you can scheduled this procedure to run using dbms_jobs or from cron job (using unix)
DECLARE
      TYPE partrec IS RECORD (
         table_name        varchar2 (30),
         partition_name    varchar2 (30),
         upperbound        varchar2 (83),
         tablespace_name   varchar2 (70)
      );

      CURSOR p_cur
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE high_value_length > 2
                ORDER BY partition_position DESC;

      CURSOR part_cur (i_table_name IN varchar2)
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE table_name = i_table_name
         ORDER BY partition_position DESC;

      prec                         partrec;
      v_last_partition             varchar2 (50);
      v_next_partition             varchar2 (50);
      v_last_part_date             varchar2 (50);
      v_next_part_date             date;
      v_part_tmp_date              date;
      v_part_date                  date;
      v_initial_date               date;
      i_initial_date               date;
      v_sql                        varchar2 (300);
      v_initial_part               varchar2 (30);
      i_initial_part               varchar2 (30);

      c_dummy_partition   CONSTANT varchar2 (5)   := 'DUMMY';
      parent_tname                 varchar2 (30);
      v_missing_months             number;
      i                            number;

   BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';
      --
      -- Adding partitions to existing range partitioned tables
      --
      FOR cur IN p_cur
      LOOP
         OPEN part_cur (cur.table_name);
         FETCH part_cur
          INTO prec;
        CLOSE part_cur;
         v_last_partition := prec.partition_name;
        -- DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition);
         v_last_part_date := SUBSTR (prec.upperbound, 10, 20);
        -- DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date);
         v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS');
        -- DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date);
         v_part_tmp_date := v_part_date;
         v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date);

        -- NOTE : MODIFY following line to create partition in one month or n month in advance.
         v_missing_months := v_missing_months + 12; 

        -- DBMS_OUTPUT.put_line
                  --        (   'No of monthly partitions  will be created is:'
                     --      || CEIL (v_missing_months)
                     --     );

         FOR i IN 1 .. v_missing_months
         LOOP
            IF (v_part_date = LAST_DAY (v_part_date + i))
            THEN
               v_part_date := v_part_date + i;
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            ELSE
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            END IF;
            v_part_tmp_date := v_part_tmp_date + i;
            DBMS_OUTPUT.put_line (   'Next partition Date = '
                                || TO_CHAR (v_next_part_date, 'YYYYMM')
                             );

            v_next_partition :=
               REPLACE (v_last_partition,
                        SUBSTR (v_last_partition, -6),
                        TO_CHAR (v_next_part_date - i, 'YYYYMM')
                       );

            DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition);
            v_sql :=
                  'ALTER TABLE '
               || prec.table_name
               || ' ADD PARTITION '
               || v_next_partition
               || ' VALUES LESS THAN ( TO_DATE('''
               || v_next_part_date
               || ' 00:00:00'', ''YYYYMMDD HH24:MI:SS''))'
               || ' tablespace '
               || prec.tablespace_name;

  DBMS_OUTPUT.put_line (v_sql);
           EXECUTE IMMEDIATE v_sql;
            COMMIT;
        END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 300));
         RAISE;
   END;
Regards
1 person found this helpful
676821
Thanks a lot for all the replies.

Altough they were all helpful, for some reason, I was asked not to use JOBS (if possible).

After APC confirmed my first solution would not work I gave it some thought and came up with another approach which might not be the most appropriate solution in this case, but the result was achieved without JOBS.

Solution I´m using:

The lower partition, which is created with the table, will hold the current month´s records and the previous ones;

A BEFORE INSERT TRIGGER will call an AUTONOMOUS TRANSACTION PROCEDURE that check if next month´s partition (relative to the record that´s being inserted) is already created. If it´s not, the procedure then creates it;

The insertion now will work, as the partition created outside the transaction is for next month. This way the partition for the current month is always going to be available.

PS.: It might be important to point out that this solution only works in this situation because the business rules ensure that there will never be a record with a date greater than the current date. If this rule didn´t exist the solution would not be suitable.

Thanks again.

Bruno
1 - 5

Post Details

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