oracle 11g中分区表的dml错误记录

Question

当我们尝试在分区表中插入数据时使用DML错误日志记录时，我们面临一些问题。

在我们的程序中，我们在某些目标表中从不同的表中加载数据。在我们的一个程序中，我们在一个表中插入数据

• 是分区表。
• 有一个唯一的索引
• 对一个或多个列没有空约束。

目前，在最后一个表中插入数据之前，我们不会删除违反NULL/ UNIQUE约束的记录。当我们尝试插入违反NULL约束或唯一约束的记录时，我们会在错误日志表中得到一些重复的记录。

例如。如果有2条记录违反NULL约束，1条记录违反唯一约束，那么错误日志表应该有3条记录。但这里有四五张唱片。

甲骨文版本详情-

Oracle数据库11g企业版发布11.2.0.3.0 -64位生产PL/SQL版本11.2.0.3.0 -生产核心11.2.0.3.0

下面是创建复制此问题的表的示例查询

/* Sample table & index creation -- START */
drop table tmp_table purge;
create table tmp_table
(
   CREATE_DT       date 
  ,REC_SEQ_NBR    number( 12 ) 
  ,REC_TYP_CD     varchar2( 3 byte )
  ,EMP_NBR        number( 6 )
  ,DEPT_NBR       number( 8 )
);
-- create final table with partitions to insert data into it
drop table tmp_final_table purge;
create table tmp_final_table
(
   CREATE_DT       date not null
  ,REC_SEQ_NBR    number( 12 ) not null
  ,REC_TYP_CD     varchar2( 3 byte ) not null
  ,EMP_NBR        number( 6 )
  ,DEPT_NBR       number( 8 )
)
nocompress
tablespace ALL_DATA_4M_01_D
result_cache (mode default)
pctused 0
pctfree 5
initrans 1
maxtrans 255
storage( buffer_pool default flash_cache default cell_flash_cache default )
partition by range
   (create_dt)
   (
      partition
         PM201603
         values less than
            (to_date( ' 2016-03-01 00:00:00'
                     ,'SYYYY-MM-DD HH24:MI:SS'
                     ,'NLS_CALENDAR=GREGORIAN'
                     ))
         logging
         nocompress
         tablespace ALL_DATA_4M_01_D
         pctfree 5
         initrans 1
         maxtrans 255
         storage( initial 4 m
                  next 4 m
                  maxsize unlimited
                  minextents 1
                  maxextents unlimited
                  pctincrease 0
                  buffer_pool default
                  flash_cache default
                  cell_flash_cache default
                 )
     ,partition
         PM201606
         values less than
            (to_date( ' 2016-06-01 00:00:00'
                     ,'SYYYY-MM-DD HH24:MI:SS'
                     ,'NLS_CALENDAR=GREGORIAN'
                     ))
         logging
         nocompress
         tablespace ALL_DATA_4M_01_D
         pctfree 5
         initrans 1
         maxtrans 255
         storage( initial 4 m
                  next 4 m
                  maxsize unlimited
                  minextents 1
                  maxextents unlimited
                  pctincrease 0
                  buffer_pool default
                  flash_cache default
                  cell_flash_cache default
                 )
     ,partition
         PM201609
         values less than
            (to_date( ' 2016-09-01 00:00:00'
                     ,'SYYYY-MM-DD HH24:MI:SS'
                     ,'NLS_CALENDAR=GREGORIAN'
                     ))
         logging
         nocompress
         tablespace ALL_DATA_4M_01_D
         pctfree 5
         initrans 1
         maxtrans 255
         storage( initial 4 m
                  next 4 m
                  maxsize unlimited
                  minextents 1
                  maxextents unlimited
                  pctincrease 0
                  buffer_pool default
                  flash_cache default
                  cell_flash_cache default
                 ) )
nocache
noparallel
monitoring;
-- create a primary key
create unique index tmp_final_table_pk
   on dkharwandikar.tmp_final_table( create_dt, rec_seq_nbr )
   logging
   tablespace ALL_DATA_4M_01_I
   pctfree 10
   initrans 2
   maxtrans 255
   storage( initial 4 m
            next 4 m
            maxsize unlimited
            minextents 1
            maxextents unlimited
            pctincrease 0
            buffer_pool default
            flash_cache default
            cell_flash_cache default
           )
   noparallel;
;
/* Sample table & index creation -- END */
/* PLSQL block to insert data in the final table - START */
begin
   insert /*+ parallel(6) */
         into tmp_final_table( create_dt
                              ,REC_SEQ_NBR
                              ,REC_TYP_CD
                              ,EMP_NBR
                              ,DEPT_NBR
                              )
      select create_dt
            ,REC_SEQ_NBR
            ,REC_TYP_CD
            ,emp_nbr
            ,dept_nbr
      from   tmp_table
   log    errors into err$_tmp_final_table ( 'INSERT' )
             reject limit unlimited;
   commit;
end;
/* PLSQL block to insert data in the final table - END */

下面是不同的场景和结果(请截断/删除&在测试下面提到的每个场景之前创建表)

-- Case 1- Few records violating UNIQUE constraint and few records violating NULL constraint
-- Result- For each record violating NULL constraint, we can see 2 records in the error log table.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some records with REC_TYP_CD value as NULL. This is having NULL constraint on final table
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 11, null, 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, null, 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 12, null, 3, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
COMMIT;
--
-- Case 2- No record violating NULL constraint; few records with UNIQUE constraint violation
-- Result- For first record causing duplicate, we can see 2 records in the error log table. So, in below example, we should get 3 records in error log but we get 4 records.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
COMMIT;

--
-- Case 3- Try to insert duplicate records by running insert query twice. 
-- Result- For first run, it will insert all the data & in 2nd run it will insert all records in error log. In error log you can see one additional record is present.
-- Use below data set to test this scenario
-- In below example, in 2nd run, we should get 10 records in error log. But we get 11 records in error log.
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
COMMIT;
--
-- Case 4- Few records violating NULL constraint; no records with UNIQUE constraint violation
-- Result- For each record violating NULL constraint we can see 1 records in the error log table.
-- No Issue
--
-- Case 4- Use any of the scenario (case 1, 2 or 3) data setup with CURSOR FORALL-BULK COLLECT- INSERT
-- Result- For each record violating constraint we can see 1 records in the error log table.
-- No Issue
-- code to try this scenario
declare
   type t_create_dt is table of tmp_final_table.create_dt%type
      index by binary_integer;
   v_create_dt    t_create_dt;
   type t_rec_seq_nbr is table of tmp_final_table.rec_seq_nbr%type
      index by binary_integer;
   v_rec_seq_nbr  t_rec_seq_nbr;
   type t_rec_typ_cd is table of tmp_final_table.rec_typ_cd%type
      index by binary_integer;
   v_rec_typ_cd   t_rec_typ_cd;
   type t_emp_nbr is table of tmp_final_table.emp_nbr%type
      index by binary_integer;
   v_emp_nbr      t_emp_nbr;
   type t_dept_nbr is table of tmp_final_table.dept_nbr%type
      index by binary_integer;
   v_dept_nbr     t_dept_nbr;
   type t_cmd_cur is ref cursor;
   cmd_cur        t_cmd_cur;
   v_sql_stmt     varchar2 ( 2500 ) := null;
   n_commit_cnt   number := 2;
   n_tot_rec_load_cnt number := 0;
begin
   v_sql_stmt  := ' select create_dt
                          ,rec_seq_nbr
                          ,rec_typ_cd
                          ,emp_nbr
                          ,dept_nbr
                    from   tmp_table ';
   open cmd_cur for v_sql_stmt;
   loop
      fetch cmd_cur
         bulk collect into v_create_dt
             ,v_rec_seq_nbr
             ,v_rec_typ_cd
             ,v_emp_nbr
             ,v_dept_nbr
         limit n_commit_cnt;
      if cmd_cur%rowcount > n_tot_rec_load_cnt
      then
         forall j in v_create_dt.first .. v_create_dt.last
            insert
            into   tmp_final_table ( create_dt
                                    ,rec_seq_nbr
                                    ,rec_typ_cd
                                    ,emp_nbr
                                    ,dept_nbr
                                    )
               values (
                         v_create_dt ( j )
                        ,v_rec_seq_nbr ( j )
                        ,v_rec_typ_cd ( j )
                        ,v_emp_nbr ( j )
                        ,v_dept_nbr ( j ) )
            log    errors into err$_tmp_final_table ( 'INSERT' )
                      reject limit unlimited;

         n_tot_rec_load_cnt := n_tot_rec_load_cnt + sql%rowcount;
      end if;
      commit;
      exit when cmd_cur%notfound;
   end loop;
   close cmd_cur;
   commit;
end;

我知道我们可以处理这些问题-- tmp_table本身，并删除导致问题的记录。但我想知道这里出了什么问题？只有当我们在分区表上使用它时，才会发生这种情况。如果我们从最后一个表中删除分区，问题就会得到解决。

我们在许多程序中遵循类似的逻辑和表/索引设置，我希望避免修改这些程序。是预期的行为吗？

在一个oracle论坛中，我看到错误表中的唯一约束对于直接路径插入有不同的效果。我相信我不是在这里直接插入。

如果你需要更多的信息，请告诉我。

Answer 1

想办法解决这个问题。只有当错误表中记录为零时，才会发生此问题。因此，我们首先将一个虚拟记录插入到错误表中，在加载数据之后，我们删除了这个虚拟记录。这似乎解决了这个问题。